前言：
在日常数据库运维过程中，可能经常会用到各种拼接语句，巧用拼接SQL可以让我们的工作方便很多，达到事半功倍的效果。本篇文章将会分享几个日常会用到的SQL拼接案例，类似的SQL还可以举一反三，探索出更多的可能性哦。
注意：适用于5.7版本，其他版本可能稍许不同。
1.CONCAT函数介绍
授人以鱼不如授人以渔，拼接SQL主要用到的是CONCAT函数，我们先来介绍下该函数的用法。
CONCAT(s1,s2…,sn) 是合并字符串函数，返回结果为连接参数产生的字符串，参数可以是一个或多个，若有任何一个参数为 NULL，则返回值为 NULL。当拼接字符串中有 ‘ 时，要用 \ 转义，貌似用两个单引号也行，不过还是推荐用 \ 转义，下面用几个示例来了解下CONCAT函数的用法。
mysql> SELECT CONCAT('MySQL','5.7'),CONCAT('MySQL',NULL),CONCAT('\'MySQL\'');
+-----------------------+----------------------+---------------------+
| CONCAT('MySQL','5.7') | CONCAT('MySQL',NULL) | CONCAT('\'MySQL\'') |
+-----------------------+----------------------+---------------------+
| MySQL5.7 | NULL | 'MySQL' |
+-----------------------+----------------------+---------------------+
简单介绍完CONCAT函数的使用方法后，下面分享几个用到SQL拼接的场景，也许在你工作中会用到哦。
2.拼接查询所有用户
SELECT DISTINCT
CONCAT(
'User: \'',
USER,
'\'@\'',
HOST,
'\';'
) AS QUERY
FROM
mysql.USER;
3.拼接创建用户的语句
# 有密码字符串 在其他实例执行 可直接创建出与本实例相同密码的用户
SELECT
CONCAT(
'create user \'',
user,
'\'@\'',
Host,
'\' IDENTIFIED BY PASSWORD \'',
authentication_string,
'\';'
) AS CreateUserQuery
FROM
mysql.`user`
WHERE
`User` NOT IN (
'root',
'mysql.session',
'mysql.sys'
);
# 这样拼接也可以 带有密码认证插件
SELECT
CONCAT(
'create user \'',
user,
'\'@\'',
Host,
'\' IDENTIFIED WITH \'',
plugin,
'\' AS \'',
authentication_string,
'\';'
) AS CreateUserQuery
FROM
mysql.`user`
WHERE
`User` NOT IN (
'root',
'mysql.session',
'mysql.sys'
);
4.拼接show grants语句查询用户权限
SELECT
CONCAT(
'show grants for \'',
user,
'\'@\'',
Host,
'\';'
) AS ShowGrants
FROM
mysql.`user`
WHERE
`User` NOT IN (
'root',
'mysql.session',
'mysql.sys'
);
5.拼接创建数据库语句
SELECT
CONCAT(
'create database if not exists ',
'`',
SCHEMA_NAME,
'`',
' DEFAULT CHARACTER SET ',
DEFAULT_CHARACTER_SET_NAME,
';'
) AS CreateDatabaseQuery
FROM
information_schema.SCHEMATA
WHERE
SCHEMA_NAME NOT IN (
'information_schema',
'performance_schema',
'mysql',
'sys'
);
6.拼接DROP table
SELECT
CONCAT(
'DROP table ',
TABLE_NAME,
';'
)
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA = 'testdb' and TABLE_TYPE = 'BASE TABLE';
7.拼接kill连接
# 可以组合改变条件
SELECT
concat( 'KILL ', id, ';' )
FROM
information_schema.PROCESSLIST
WHERE
STATE LIKE 'Sending data';
SELECT
concat( 'KILL ', id, ';' )
FROM
information_schema.`PROCESSLIST`
WHERE
Command = 'Sleep'
AND TIME > 2000;
8.查看数据库大小
# 查看整个实例大小
SELECT
concat( round( sum( data_length / 1024 / 1024 ), 2 ), 'MB' ) AS data_length_MB,
concat( round( sum( index_length / 1024 / 1024 ), 2 ), 'MB' ) AS index_length_MB
FROM
information_schema.TABLES;
# 查看各个库大小
SELECT
TABLE_SCHEMA,
concat( TRUNCATE ( sum( data_length )/ 1024 / 1024, 2 ), ' MB' ) AS data_size,
concat( TRUNCATE ( sum( index_length )/ 1024 / 1024, 2 ), 'MB' ) AS index_size
FROM
information_schema.TABLES
GROUP BY
TABLE_SCHEMA
ORDER BY
data_length DESC;
9.查找表碎片
SELECT t.TABLE_SCHEMA,
t.TABLE_NAME,
t.TABLE_ROWS,
concat(round(t.DATA_LENGTH / 1024 / 1024, 2), 'M') AS size,
t.INDEX_LENGTH,
concat(round(t.DATA_FREE / 1024 / 1024, 2), 'M') AS datafree
FROM information_schema.tables t
WHERE t.TABLE_SCHEMA = 'testdb' order by DATA_LENGTH desc;
10.查找无主键表及增加自增ID作为主键
# 查找出无主键的表
SELECT
t1.table_schema,
t1.table_name
FROM
information_schema.TABLES t1
LEFT OUTER JOIN information_schema.TABLE_CONSTRAINTS t2 ON t1.table_schema = t2.TABLE_SCHEMA
AND t1.table_name = t2.TABLE_NAME
AND t2.CONSTRAINT_NAME IN ('PRIMARY')
WHERE
t2.table_name IS NULL
AND t1.TABLE_SCHEMA NOT IN (
'information_schema',
'performance_schema',
'mysql',
'sys'
) ;
# 拼接出增加自增ID作为主键的SQL
SELECT
CONCAT('ALTER TABLE ',t1.table_schema,'.',t1.table_name,' ADD COLUMN increment_id INT UNSIGNED NOT NULL auto_increment COMMENT \'自增主键\' PRIMARY KEY FIRST;')
FROM
information_schema.TABLES t1
LEFT OUTER JOIN information_schema.TABLE_CONSTRAINTS t2 ON t1.table_schema = t2.TABLE_SCHEMA
AND t1.table_name = t2.TABLE_NAME
AND t2.CONSTRAINT_NAME IN ('PRIMARY')
WHERE
t2.table_name IS NULL
AND t1.table_type = 'BASE TABLE'
AND t1.TABLE_SCHEMA NOT IN (
'information_schema',
'performance_schema',
'mysql',
'sys'
) ;
11.查找大写表及转为小写表
# 若lower_case_table_names=0可能导致表名既有大写又有小写，
# 想将lower_case_table_names设为1的话 需要先将大写的表和视图名称改为小写的。
# 查找出名称为大写的表和视图
SELECT
TABLE_SCHEMA,
TABLE_NAME,
TABLE_TYPE
FROM
information_schema.`TABLES`
WHERE
TABLE_SCHEMA NOT IN ( 'information_schema', 'sys', 'mysql', 'performance_schema' )
AND TABLE_NAME REGEXP BINARY '[A-Z]';
# 拼接出大写表名改为小写的SQL
SELECT
CONCAT( 'rename table ', TABLE_SCHEMA, '.', TABLE_NAME, ' to ', TABLE_SCHEMA, '.', LOWER( TABLE_NAME ), ';' )
FROM
information_schema.`TABLES`
WHERE
TABLE_SCHEMA NOT IN ( 'information_schema', 'sys', 'mysql', 'performance_schema' )
AND TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME REGEXP BINARY '[A-Z]';
上次更新时间 13 3 月, 2023 at 09:59 上午