MySQL运维实用SQL

1. 利用concat()函数拼接

MySQL中concat大法，可以无需游标，各种拼接，复制出来即用。
下面是一些常用的例子。

1.1 批量修改一个库表名为小写或大写

有些应用系统在开发阶段使用的可能是开发人员本地安装的windows环境的MySQL，表名是不区分大小写的；而一般生产环境的MySQL部署在Linux环境，lower_case_table_names默认值为 0，即表名的存储、以及表名的比较都是区分大小写的，因此数据库迁移至Linux环境后需要将表名改为和程序大小写匹配。
改为小写：

SELECT concat('alter table ', TABLE_NAME, ' rename to ', LOWER(TABLE_NAME),';') 
FROM information_schema.TABLES WHERE TABLE_SCHEMA='dbname';

改为大写

SELECT concat('alter table ', TABLE_NAME, ' rename to ', upper(TABLE_NAME),';') 
FROM information_schema.TABLES WHERE TABLE_SCHEMA='dbname';

1.2 按照特定规则修改表结构

以增加列和索引为例。此例的场景为：原主键是id列，是由程序随机生成的字符串；出于性能考虑现需将原id列的主键取消，改为唯一键，添加唯一索引；而新增一个自增的数字列id_int作为代理主键。

SELECT 
concat(
    'ALTER TABLE `', 
    TABLE_NAME, 
    '` ADD COLUMN `id_int` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT FIRST, DROP PRIMARY KEY, ADD PRIMARY KEY (`id_int`), ADD UNIQUE INDEX `id` (`id`);'
)
FROM information_schema.columns
WHERE column_name = 'id' AND table_schema = 'dbname';

再如修改某库id_int列：

SELECT 
concat(
    'ALTER TABLE `', 
    TABLE_NAME, 
    '` CHANGE COLUMN `id_int` `id_int` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT FIRST;'
)
FROM information_schema.columns
WHERE column_name = 'id_int' AND table_schema = 'dbname' AND  column_default = '0';

再如按特定规则批量修改collation：

SELECT 
concat(
    'ALTER TABLE `', 
    TABLE_NAME, 
    '` CHANGE COLUMN `',
    COLUMN_NAME,
    '`  `',
    COLUMN_NAME,
    '` ', 
    COLUMN_TYPE,
    ' NULL DEFAULT NULL COLLATE ''utf8mb3_general_ci'';'    
)
FROM information_schema.columns
WHERE collation_name = 'utf8mb3_bin'
AND table_schema = 'dbname'
AND TABLE_NAME NOT LIKE 'ACT_%'
AND column_default IS NULL;

再如批量修改表的行格式：

SELECT 
concat(
    'ALTER TABLE `', 
    TABLE_NAME, 
    '` ROW_FORMAT=DYNAMIC;'
)
FROM information_schema.tables
WHERE  table_schema = 'dbname'
AND TABLE_NAME NOT LIKE 'ACT_%'
AND ROW_FORMAT = 'Compact';

其他修改表结构的语句均可参考此例进行修改。

1.3 统计所有表行数

由于统计信息可能更新不及时，所以有时候还是需要count的方式统计某库所有表数据量。

select concat(
    'select "', 
    TABLE_name, 
    '", count(*) from `', 
    TABLE_SCHEMA, 
    '`.',
    TABLE_name,
    ' union all'
) from information_schema.tables 
where TABLE_SCHEMA='dbname';

1.4 清除多表数据

此例为清除一个库所有表数据。

SELECT 
concat(
    'truncate TABLE `', 
    TABLE_NAME, 
    '` ;'
)
FROM information_schema.tables
WHERE  table_schema = 'dbname';

当然也有很多其他方式清除所有数据，比如单独备份表结构再覆盖导入等。
此例配合其他条件可清除部分表数据。

2. 排障

只列举最常用的一些语句，其余可参考上周写的另一篇MySQL8.0线程排障实用SQL

2.1 以特定条件杀会话

此处也用GROUP_CONCAT()函数拼接，此例为杀掉sleep的会话：

SELECT GROUP_CONCAT(CONCAT('kill ',id) SEPARATOR '; ') AS cmd FROM information_schema.processlist 
WHERE USER='xxx' 
AND command='Sleep'
AND db='dbname'

按其余条件查杀也可参考此例进行修改。

2.2 查找阻塞

SELECT
b.trx_mysql_thread_id as 被阻塞id,
b.trx_query as 被阻塞sql,
c.trx_mysql_thread_id as 阻塞id,
c.trx_query as 阻塞sql,
d.OBJECT_SCHEMA,
d.OBJECT_NAME,
d.INDEX_NAME
FROM performance_schema.data_lock_waits a
LEFT JOIN
information_schema.INNODB_TRX b ON a.REQUESTING_ENGINE_TRANSACTION_ID = b.trx_id
LEFT JOIN
information_schema.INNODB_TRX c ON a.BLOCKING_ENGINE_TRANSACTION_ID = c.trx_id
LEFT JOIN
performance_schema.data_locks d ON a.REQUESTING_ENGINE_LOCK_ID = d.ENGINE_LOCK_ID
;

此例中的系统视图适用于MySQL8.0，其中trx_mysql_thread_id与processlist中id相同，用于kill。

2.3 查看占元数据锁的线程

SELECT a.OWNER_THREAD_ID,b.PROCESSLIST_INFO,b.processlist_id
from performance_schema.metadata_locks a
INNER join performance_schema.threads b 
ON a.OWNER_THREAD_ID = b.THREAD_ID
WHERE a.object_name = 'xxx'
;

其中processlist_id用于kill。

3. 其他

3.1 查看非innodb存储引擎的表

经常用于预发布环境上生产时，验证表结构是否符合MGR要求

select table_catalog
      ,table_schema
      ,table_name
      ,engine
from information_schema.tables
where engine<>'InnoDB'
and table_schema not in ('mysql','information_schema','performance_schema');

3.2 查看一个库无主键的表

同样用于预发布环境上生产时，验证表结构是否符合MGR要求

select  table_name
from information_schema.tables
where table_name not in (
                        select distinct table_name
                        from information_schema.columns
                        where column_key = "PRI"
                        )
AND table_schema = 'dbname';

文章搬运来源:https://www.modb.pro/db/1855806933815537664?utm_source=index_ai

个人学习使用，如有侵权，请联系本人删除。