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;

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

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;