03.扩展SQL.md

title	date	categories	tags	permalink
扩展 SQL	2020-10-10 12:03:05 -0700	数据库 关系型数据库 综合	数据库 关系型数据库 SQL	/pages/55e9a7/

扩展 SQL

数据库

表

查看表的基本信息

SELECT * FROM information_schema.tables
WHERE table_schema = 'test' AND table_name = 'user';

查看表的列信息

SELECT * FROM information_schema.columns
WHERE table_schema = 'test' AND table_name = 'user';

如何批量删除大量数据

如果要根据时间范围批量删除大量数据，最简单的语句如下：

delete from orders
where timestamp < SUBDATE(CURDATE(),INTERVAL 3 month);

上面的语句，大概率执行会报错，提示删除失败，因为需要删除的数据量太大了，所以需要分批删除。

可以先通过一次查询，找到符合条件的历史订单中最大的那个订单 ID，然后在删除语句中把删除的条件转换成按主键删除。

select max(id) from orders
where timestamp < SUBDATE(CURDATE(),INTERVAL 3 month);

-- 分批删除，? 填上一条语句查到的最大 ID
delete from orders
where id <= ?
order by id limit 1000;

修改表的编码格式

utf8mb4 编码是 utf8 编码的超集，兼容 utf8，并且能存储 4 字节的表情字符。如果表的编码指定为 utf8，在保存 emoji 字段时会报错。

ALTER TABLE <tableName> CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

其他

显示哪些线程正在运行

mysql> show processlist;
+----+-----------------+-----------------+------+---------+-------+------------------------+------------------+
| Id | User            | Host            | db   | Command | Time  | State                  | Info             |
+----+-----------------+-----------------+------+---------+-------+------------------------+------------------+
|  5 | event_scheduler | localhost       | NULL | Daemon  | 40230 | Waiting on empty queue | NULL             |
| 10 | root            | localhost:10120 | NULL | Query   |     0 | init                   | show processlist |
+----+-----------------+-----------------+------+---------+-------+------------------------+------------------+
2 rows in set (0.00 sec)

Mysql 连接完成后，如果你没有后续的动作，这个连接就处于空闲状态，你可以在 show processlist 命令中看到它。其中的 Command 列显示为“Sleep”的这一行，就表示现在系统里面有一个空闲连接。客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值是 8 小时。

参考资料

• 《SQL 必知必会》