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

Introduction

I have been using MYSQL for years and discovered many useful tricks that I am sharing here.

1. group_concat

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

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

The result combines identical name rows into a comma‑separated string.

2. char_length

To sort records by the length of a string, use char_length:

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

This orders the rows by the character count of name.

3. locate

Find the position of a substring within a string with locate:

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

If lengths are equal, rows are further ordered by the leftmost occurrence of the keyword.

4. replace

Replace parts of a string using replace:

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

You can also remove spaces or modify JSON content with the same function.

5. now

Retrieve the current timestamp with now() or include milliseconds with now(3):

select now() from brand limit 1;

select now(3) from brand limit 1;

6. insert into ... select

Insert a large batch of data from another table in a single statement:

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

Skip inserting rows that would violate a unique index using INSERT IGNORE:

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

8. select ... for update

Lock a row within a transaction to prevent concurrent updates:

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

The row must be selected by primary key or unique index for the lock to be effective.

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

Be aware of potential deadlocks under high concurrency.

10. show create table

View the full CREATE TABLE statement, including indexes and engine details:

show create table `order`;

11. create table ... select

Create a new table and populate it from an existing one in one step:

create table order_2022121820 select * from `order`;

12. explain

Analyze query execution plans and index usage with explain:

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

13. show processlist

Inspect current MySQL threads to identify long‑running or problematic queries:

show processlist;

14. mysqldump

Export tables or databases to SQL files for backup and restoration:

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