-- 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`;