MySQL 常用语法/语句

DDL

not null
unique
primary key
auto increment

1、建表时添加约束

CREATE TABLE t_user(
  user_id INT(10) NOT NULL
);

2、建表后，通过 alter 添加约束

alter table t_user modify user_id INT(10) NOT NULL;

3、删除约束

alter table t_user modify user_id INT(10);

4、新增字段

向 table_name 表的 old_column 字段后面新增一个类型为 VARCHAR(100)，默认为 null 的 column_name 字段。

ALTER TABLE table_name ADD COLUMN column_name VARCHAR(100) DEFAULT NULL COMMENT '新加字段' AFTER old_column;

5、删除列

删除表 t 的 c 列。

alter table t drop column c;

6、重命名表

将表 t1 重命名为 t2

alter table t1 rename t2;

7、重命名列

将表 t1 中 old_column 字段修改为 new_column

alter table t1 change old_column new_column varchar(100) null comment '新列xxx';

8、修改字段类型/长度

将 table_name 的 column_name 字段的类型修改为 varchar(100)

alter table table_name modify column_name varchar(100)

系统信息

1、查询数据库死锁发生的次数

SELECT `count` FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME='lock_deadlocks';

2、查询 InnoDB 持有锁的信息

8.0之前

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS\G;

8.0之后

select * from performance_schema.data_locks\G;

3、查看最后一次发生死锁的日志

SHOW ENGINE INNODB STATUS;

4、engine

查看数据页的大小,默认是 16kb

show variables like '%innodb_page_size%';

5、SQL_NO_CACHE

在执行 SQL 查询时用来指示 MySQL 不要返回缓存的结果，而是重新执行查询。

SELECT SQL_NO_CACHE * FROM your_table WHERE your_condition;

6、show variables 和 show status 的区别

show status 一般在查看 MySQL 运行时的状态信息。

show variables 命令用于查看 MySQL 服务器的系统变量。

事务

1、查看事务隔离级别

show variables like 'transaction_isolation';

日志

1、redo log

查看每个 redo log 的大小

SHOW VARIABLES LIKE 'innodb_log_file_size';

查看 redo log 的数量

SHOW VARIABLES LIKE 'innodb_log_files_in_group';

redo log 缓冲区大小

SHOW VARIABLES LIKE 'innodb_log_buffer_size';

2、slow query log

查看是否开启慢查询日志

SHOW VARIABLES LIKE 'slow_query_log';

查看慢查询日志的保存路径

SHOW VARIABLES LIKE 'slow_query_log_file';

慢查询的阈值

SHOW VARIABLES LIKE 'long_query_time';

MySQL SQL 技巧

1、case ... when ...

第一种写法

CASE value
    WHEN compare_value THEN result
    [WHEN compare_value THEN result ...]
    [ELSE result]
END

第二种写法

CASE
    WHEN condition THEN result
    [WHEN condition THEN result ...]
    [ELSE result]
END

2、构建测试数据

表结构

CREATE TABLE s1 (
	id INT,
	key1 VARCHAR (100),
	key2 INT,
	key3 VARCHAR (100),
	key_part1 VARCHAR (100),
	key_part2 VARCHAR (100),
	key_part3 VARCHAR (100),
	common_field VARCHAR (100),
	PRIMARY KEY (id),
	INDEX idx_key1 (key1),
	UNIQUE INDEX idx_key2 (key2),
	INDEX idx_key3 (key3),
	INDEX idx_key_part (key_part1, key_part2, key_part3) 
) ENGINE = INNODB CHARSET = utf8;
 
 
CREATE TABLE s2 (
	id INT AUTO_INCREMENT,
	key1 VARCHAR ( 100 ),
	key2 INT,
	key3 VARCHAR ( 100 ),
	key_part1 VARCHAR ( 100 ),
	key_part2 VARCHAR ( 100 ),
	key_part3 VARCHAR ( 100 ),
	common_field VARCHAR ( 100 ),
	PRIMARY KEY ( id ),
	INDEX idx_key1 ( key1 ),
	UNIQUE INDEX idx_key2 ( key2 ),
	INDEX idx_key3 ( key3 ),
	INDEX idx_key_part ( key_part1, key_part2, key_part3 ) 
) ENGINE = INNODB CHARSET = utf8;

设置参数 log_bin_trust_function_creators

创建函数，假如报错，需开启如下命令：允许创建函数设置：

set global log_bin_trust_function_creators=1;  # 不加global只是当前窗口有效

创建函数

DELIMITER //
CREATE FUNCTION rand_string1(n INT)
RETURNS VARCHAR(255) #该函数会返回一个字符串
BEGIN
	DECLARE chars_str VARCHAR(100) DEFAULT
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
	DECLARE return_str VARCHAR(255) DEFAULT '';
	DECLARE i INT DEFAULT 0;
	WHILE i < n DO
		SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
		SET i = i + 1;
	END WHILE;
	RETURN return_str;
END //
DELIMITER ;

创建存储过程

s1 表

DELIMITER
$
CREATE PROCEDURE insert_s1(IN min_num INT (10), IN max_num INT (10))
BEGIN
    DECLARE
i INT DEFAULT 0;
    SET
autocommit = 0;
    REPEAT
SET i = i + 1;
INSERT INTO s1
VALUES ((min_num + i),
        rand_string1(6),
        (min_num + 30 * i + 5),
        rand_string1(6),
        rand_string1(10),
        rand_string1(5),
        rand_string1(10),
        rand_string1(10));
UNTIL
i = max_num
END REPEAT;
COMMIT;
END $
DELIMITER ;

s2 表

DELIMITER //
CREATE PROCEDURE insert_s2(IN min_num INT(10), IN max_num INT(10))
BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    REPEAT
        SET i = i + 1;
        INSERT INTO s2
        VALUES ((min_num + i),
                rand_string1(6),
                (min_num + 30 * i + 5),
                rand_string1(6),
                rand_string1(10),
                rand_string1(5),
                rand_string1(10),
                rand_string1(10));
    UNTIL i = max_num
        END REPEAT;
    COMMIT;
END //
DELIMITER

插入数据

CALL insert_s1(10001,10000);

CALL insert_s2(10001,10000);

优化

1. 使用 `coalesce` 代替 `union`

coalesce 用于获取第一个非空值。

优化前的 SQL :

SELECT IFNULL(sum(t.rule_code = 'TransformerOverload'), 0) as transformerOverload,
       IFNULL(sum(t.rule_code = 'HighOilTemperature'), 0)  as highOilTemperature,
       IFNULL(sum(t.rule_code = 'Low/HighVoltage'), 0)     as lowHighVoltage,
       IFNULL(sum(t.rule_code = 'Low/HighFrequency'), 0)   as lowHighFrequency,
       IFNULL(sum(t.rule_code = '3PhaseUnbalanced'), 0)    as phaseUnbalanced,
       IFNULL(sum(t.rule_code = 'LowFrequency'), 0)        as lowFrequency
FROM (select ar.id,
             aru.rule_code,
             ar.occur_time
      FROM alarm_record ar
               left join `alarm_rule` aru on aru.id = ar.rule_id
      WHERE ar.alarm_status = 2
        and aru.rule_code is not null
      union all
      select ar.id,
             ip.alarm_code rule_code,
             ar.occur_time
      FROM alarm_record ar
               left join import_point ip on ar.import_point_id = ip.id
      WHERE ar.alarm_status = 2
        and ip.alarm_code is not null) t
WHERE t.rule_code IN (
                      'TransformerOverload',
                      'HighOilTemperature',
                      'Low/HighVoltage',
                      'Low/HighFrequency',
                      '3PhaseUnbalanced',
                      'LowFrequency')

优化后：将 TransformerOverload 等放入一张表内；用 coalesce 优化 union;

CREATE TABLE rule_codes
(
    rule_code VARCHAR(50) PRIMARY KEY
);

INSERT INTO rule_codes (rule_code)
VALUES ('TransformerOverload'),
       ('HighOilTemperature'),
       ('Low/HighVoltage'),
       ('Low/HighFrequency'),
       ('3PhaseUnbalanced'),
       ('LowFrequency');


SELECT rc.rule_code,
       IFNULL(SUM(t.rule_code = rc.rule_code), 0) AS occurrences
FROM (SELECT ar.id,
             COALESCE(aru.rule_code, ip.alarm_code) AS rule_code,
             ar.occur_time
      FROM alarm_record ar
               LEFT JOIN alarm_rule aru ON aru.id = ar.rule_id
               LEFT JOIN import_point ip ON ar.import_point_id = ip.id
      WHERE ar.alarm_status = 2
        AND COALESCE(aru.rule_code, ip.alarm_code) IS NOT NULL
        and ar.occur_time between '2024-08-16 00:00:00' and '2024-09-20 00:00:00') t
         RIGHT JOIN rule_codes rc ON t.rule_code = rc.rule_code
GROUP BY rc.rule_code;

2. 使用 `MyBatis-Plus` 的 `saveOrUpdateBatch` 方法时，很慢。

原因：saveOrUpdateBatch 方法是先查询，再插入或更新，如果数据量大，会很慢。

解决方法1：在 jdbc 的 url 中添加 rewriteBatchedStatements=true。解决方法2：分别使用 updateBatchById saveBatch 方法。

3. Every derived table must have its own alias

在 MySQL 中，所有的派生表都必须有别名。

解决方法：给子查询等表添加别名。

select * from (select xxx from table_name) t;

DDL​

1、建表时添加约束​

2、建表后，通过 alter 添加约束​

3、删除约束​

4、新增字段​

5、删除列​

6、重命名表​

7、重命名列​

8、 修改字段类型/长度​

系统信息​

1、查询数据库死锁发生的次数​

2、查询 InnoDB 持有锁的信息​

3、查看最后一次发生死锁的日志​

4、engine​

5、SQL_NO_CACHE​

6、show variables 和 show status 的区别​

事务​

1、查看事务隔离级别​

日志​

1、redo log​

2、slow query log​

MySQL SQL 技巧​

1、case ... when ...​

2、构建测试数据​

优化​

1. 使用 coalesce 代替 union​

2. 使用 MyBatis-Plus 的 saveOrUpdateBatch 方法时，很慢。​

3. Every derived table must have its own alias​

DDL