Master MySQL: 14 Essential Functions & Commands Every Developer Should Know

I have been using MySQL extensively for years and have collected a set of handy tricks that can simplify many common tasks.

1. group_concat

When you need to concatenate values from rows that share the same name, use the group_concat function.

select name, group_concat(code) from `user` group by name;

The result combines all code values for each identical name into a comma‑separated string.

2. char_length

Use char_length to get the length of a string and sort results based on that length.

select * from brand where name like '%苏三%' order by char_length(name) asc limit 5;

3. locate

To find the position of a substring within a string, use locate. It can be combined with other ordering criteria.

select * from brand where name like '%苏三%' order by char_length(name) asc, locate('苏三', name) asc limit 5,5;

4. replace

Replace parts of a string with replace. It can also be used to trim spaces or modify JSON content.

update brand set name = REPLACE(name, 'A', 'B') where id = 1;

update brand set name = REPLACE(name, ' ', '') where name like ' %';

5. now

Retrieve the current timestamp with now(). For millisecond precision, use now(3).

select now() from brand limit 1;

select now(3) from brand limit 1;

6. insert into ... select

Insert a large batch of rows selected from another table.

INSERT INTO `brand`(id, code, name, edit_date)
select null, code, name, now(3) from `order` where code in ('004','005');

7. insert into ... ignore

Use INSERT IGNORE to skip rows that would cause duplicate‑key errors.

INSERT ignore INTO `brand`(id, code, name, edit_date) VALUES (123, '108', '苏三', now(3));

8. select ... for update

Apply a pessimistic lock on a row within a transaction to prevent concurrent updates.

begin;
select * from `user` where id=1 for update;
-- business logic
update `user` set score = score-1 where id=1;
commit;

9. on duplicate key update

Insert a row or update it if a unique key already exists.

INSERT INTO `brand`(id, code, name, edit_date) VALUES (123, '108', '苏三', now(3))
on duplicate key update name='苏三', edit_date=now(3);

10. show create table

Display the full CREATE TABLE statement, which includes column definitions, indexes, engine, and charset.

show create table `order`;

11. create table ... select

Create a new table and populate it with data from an existing table in a single command.

create table order_2022121820 select * from `order`;

12. explain

Use EXPLAIN to view the execution plan and index usage of a query.

explain select * from `order` where code='002';

13. show processlist

Inspect current MySQL threads, their status, and running queries; you can kill problematic sessions.

show processlist;

14. mysqldump

Export a database or table to a SQL file for backup or migration.

mysqldump -h 192.22.25.226 -u root -p123456 dbname > backup.sql