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

创建表时修改自增主键，添加 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

巴特西

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

最新文章

热门文章