-- Empty a Column
UPDATE table_name SET prize = ''

-- Duplicate a Table
CREATE TABLE newtable LIKE oldtable;
INSERT newtable SELECT * FROM oldtable;

--  Convert Database Engine
ALTER TABLE bf_lyrics ENGINE = MyISAM;
ALTER TABLE bf_lyrics ENGINE = InnoDB;

--  Search FULLTEXT index
SELECT `prod_id`
FROM (`product_search`)
WHERE MATCH (`prod_title`, `prod_desc`) AGAINST ('silk' IN BOOLEAN MODE)
OR `prod_id` LIKE '%silk%' OR `prod_sku` LIKE '%silk%'
OR `prod_title` LIKE '%silk%'cat
OR `prod_desc` LIKE '%silk%';

-- Grant user permission on a Database
ALL PRIVILEGES ON mydb.* TO user@'localhost' IDENTIFIED BY 'password';GRANT 
Grant remote access to specific IP All Database
GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'1.2.3.4' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;

-- Revoke permision:
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'USERNAME'@'1.2.3.4';

-- Check permission
SELECT * from information_schema.user_privileges where grantee like "'USERNAME'%";

-- Copy table between databases ----
If you have shell access you may use mysqldump to dump the content of database1.table1 and pipe it to mysql to database2. The problem here is that table1 is still table1.
mysqldump --user=user1 --password=password1 database1 table1 \
| mysql --user=user2 --password=password2 database2
Maybe you need to rename table1 to table2 with another query. On the other way you might use sed to change table1 to table2 between the to pipes.
mysqldump --user=user1 --password=password1 database1 table1 \
| sed -e 's/`table1`/`table2`/' \
| mysql --user=user2 --password=password2 database2
If table2 already exists, you might add the parameters to the first mysqldump which dont let create the table-creates.
--\ Copy table between databases ----
mysqldump --no-create-info --no-create-db --user=user1 --password=password1 database1 table1 \
| sed -e 's/`table1`/`table2`/' \
| mysql --user=user2 --password=password2 database2

-- Import CVS
"load data local infile 'ZALORA_SG-Product_Feed.txt' into table bf_datafeed_zalora
fields terminated by ','
enclosed by '""'
lines terminated by '\n';"
Insert into a MySQL table or update if exists
INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE   
name="A", age=19
Select duplicate rows
SELECT `id` FROM `table_name` group by `simple_sku` having count(*) >= 2
Delete Dupplicate rows in MySQL
DELETE e1 FROM `bf_products_perfume` e1, `bf_products_perfume` e2 WHERE e1.`simple_sku` = e2.`simple_sku` AND e1.id > e2.id;


-- Truncate a table with foreign key
SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE table1;
SET FOREIGN_KEY_CHECKS = 1;
INSERT & Foreign key
INSERT INTO tab_teacher
  SET name_teacher = 'Dr. Smith';
INSERT INTO tab_student
  SET name_student = 'Bobby Tables',
      id_teacher_fk = LAST_INSERT_ID()

-- Search & Replace
UPDATE table_name SET field = REPLACE(field, 'foo', 'bar') WHERE INSTR(field, 'foo') > 0;
Reset ID column
SET @i=0;
UPDATE bf_amz_urls_backup SET id=(@i:=@i+1) ORDER BY `cat`;
Convert a String into a Slug
LOWER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TRIM('My String'), ':', ''), ')', ''), '(', ''), ',', ''), '\\', ''), '\/', ''), '\"', ''), '?', ''), '\'', ''), '&', ''), '!', ''), '.', ''), ' ', '-'), '--', '-'), '--', '-')) AS `post_name`


-- Select Random
SELECT t.* FROM myTable AS t JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM myTable)) AS id) AS x WHERE t.id >= x.id LIMIT 1;
Read more: https://www.warpconduit.net/2011/03/23/selecting-a-random-record-using-mysql-benchmark-results/
Advanced text searching using FULLTEXT indexes
http://www.hackingwithphp.com/9/3/18/advanced-text-searching-using-full-text-indexes
Grant all permission
GRANT ALL PRIVILEGES ON table.* TO user@'%' WITH GRANT OPTION;
Insert / Update on exist
INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE    
name="A", age=19


-- Copy data (records) between two tables:
INSERT destination_table SELECT * FROM source_table;
Select distinct rows bases on a column’s value
SELECT a.*
FROM bf_products_bags_shopee a
INNER JOIN 
  (SELECT lazada_id,
    MIN(id) as id
  FROM bf_products_bags_shopee 
  GROUP BY lazada_id
) AS b
  ON a.lazada_id = b.lazada_id
  AND a.id = b.id;

-- Change Auto-increasment Column’s Value
UPDATE bf_products_powerbanks SET `id` = `id` + 200000


-- Copy table
CREATE TABLE `new_table_name` LIKE `old_table_name`;
INSERT INTO `new_table_name` SELECT * FROM `old_table_name`;