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

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

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

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

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)