【MySQL】日常小技巧汇总，更新中……

时间：2021-04-20 08:03:34
  • 创建表时修改自增主键，添加 AUTO_INCREMENT=<Number> ，例如：
CREATE TABLE `table_name` (

  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=<Number> DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  • 建表后修改自增主键：
ALTER TABLE `table_name` AUTO_INCREMENT = 2;
  • 已有的表新增自增主键字段(id):
ALTER TABLE `table_name` ADD COLUMN id BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT FIRST
  • 修改表字段定义
ALTER TABLE `table_name`

CHANGE COLUMN `column1` `column1` VARCHAR(127) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '姓名';
  • 清除表数据
TRUNCATE TABLE `table_name`;

PS: 之前没这么清理过数据，速度贼快，酸爽！

  • 增加唯一约束
ALTER TABLE `table_name` ADD unique(`column1`, `column2`);
  • UPDATE 中有 SELECT 子查询
-- 定义变量，名字 a 可修改，用于查询记录位于第几行

SET @a = 0;

UPDATE user_info x, (

		SELECT year_name, open_id, @a := @a + 1 AS rank

		FROM user_info

		WHERE year_name = 'name'

		ORDER BY vote_count DESC, update_time ASC

	) tmp

SET x.rank = tmp.rank

WHERE x.year_name = tmp.year_name

	AND x.open_id = tmp.open_id

备注：这条语句的目的是定时刷新 user_info 表中的 rank 字段，刚开始用的 INSERT ON DUPLICATE KEY 操作的，之后老大说那样有问题，于是就思索如何优化，经测试这样写可以的。

PS: 之前没写过在 UPDATE 中使用 SELECT 子查询，因此记录。

  • 查看事务状态
SELECT * FROM information_schema.INNODB_TRX;
  • 查看表的状态
SHOW TABLE STATUS;
  • 查看 redo log 和 binlog 状态
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

SHOW VARIABLES LIKE 'sync_binlog';

PS: 二者值都建议设置为 1.

  • 长事务查询
-- 例如，大于 60 秒的

SELECT *

FROM information_schema.innodb_trx

WHERE TIME_TO_SEC(timediff(now(), trx_started)) > 60

相关文章