MySQL Code Snippet

-- 追踪优化器 Trace 功能
--  optimizer_trace_enabled=1
--  optimizer_trace_file=optimizer_trace.log
SELECT @@optimizer_trace;
SET optimizer_trace = 'enabled=on';
-- <your query>;
SET optimizer_trace = 'enabled=off';
select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;

-- 查看优化后的 SQL
-- 在联表查询时比较有效果
EXPLAIN <你的 SQL>;
SHOW WARNINGS;

-- 查看处理
SHOW PROCESSLIST;

-- 查看结构
DESC user;
SHOW COLUMNS FROM user;
DESCRIBE user;

CREATE OR REPLACE VIEW 视图名 AS SELECT[...] FROM [...]

LEFT JOIN code_value b ON a.cost_type = CONVERT ( b.`code` USING utf8mb4 ) COLLATE utf8mb4_unicode_ci

-- https://www.w3schools.com/sql/func_mysql_find_in_set.asp
select FIND_IN_SET('bb', 'aa,bb,cc');
select FIND_IN_SET(null, '0');

-- COALESCE 函数用于返回参数列表中第一个非 NULL 值。如果所有参数都为 NULL，则返回 NULL
COALESCE ( `code_value`.`name`, '' ) AS cost_type_name,

DELETE
FROM
  student
WHERE
  id NOT IN (
  SELECT
    id
FROM
  ( SELECT MIN( id ) AS id FROM student GROUP BY `name` ) tmp)

-- 创建同结构备份表
create table zzz_my_table_220727 like my_table;
-- 将需要数据写入备份表
insert into zzz_my_table_220727 select * from my_table ORDER BY id desc LIMIT 1000;
-- 情况原表
truncate table my_table;

select id from table01 where condition1 or condition2 and condition3;

-- 等价于：
select id from table01 where condition1 or (condition2 and condition3);
-- 而非：
select id from table01 where (condition1 or condition2) and condition3;

//对读取的记录加共享锁
select ... lock in share mode;

-- //对读取的记录加独占锁
select ... for update;