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

1. GROUP_CONCAT

Combine values from rows that share the same name into a single comma‑separated string.

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

The result shows each name with its associated code values concatenated.

2. CHAR_LENGTH

Get the character length of a string and sort results by that length.

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

This query returns the first five rows whose name contains "苏三", ordered from shortest to longest name.

3. LOCATE

Find the position of a substring within a string.

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

Rows are first sorted by name length, then by the leftmost occurrence of "苏三".

4. REPLACE

Replace part of a string with another string, useful for cleaning data.

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

Another example removes spaces:

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

5. NOW()

Retrieve the current timestamp (including optional fractional seconds).

select now() from brand limit 1;

select now(3) from brand limit 1;

now()

returns date and time; now(3) adds milliseconds.

6. INSERT … SELECT

Insert rows selected from another table, ideal for bulk data migration.

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

7. INSERT … IGNORE

Insert a row while silently ignoring duplicate‑key errors.

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

If a row with the same unique name already exists, the statement does nothing and reports 0 affected rows.

8. SELECT … FOR UPDATE

Lock selected rows within a transaction to prevent concurrent modifications.

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

The lock works only when the WHERE clause uses a primary key or unique index; otherwise a broader table lock may be taken.

9. ON DUPLICATE KEY UPDATE

Insert a row, or update it if a primary‑key or unique‑key conflict occurs.

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

Be cautious in high‑concurrency environments, as this pattern can lead to deadlocks.

10. SHOW CREATE TABLE

Display the full CREATE statement for a table, including 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`;

This is a quick way to back up a table.

12. EXPLAIN

Show the execution plan for a query to understand index usage. explain select * from `order` where code='002'; The output columns reveal whether indexes are used, the type of access, and estimated rows.

13. SHOW PROCESSLIST

List currently running threads, useful for spotting long‑running or stuck queries. show processlist; Columns include Id, User, Host, db, Command, Time, State, and Info. Problematic queries can be terminated with

KILL

<Id>

.

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

The generated file contains CREATE TABLE and INSERT statements that can be replayed to restore the data.