巧用 SQL 拼接语句
发布于: 2020 年 08 月 24 日
前言:
在日常数据库运维过程中,可能经常会用到各种拼接语句,巧用拼接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 QUERYFROM mysql.USER;
3.拼接创建用户的语句
# 有密码字符串 在其他实例执行 可直接创建出与本实例相同密码的用户SELECT CONCAT( 'create user \'', user, '\'@\'', Host, '\' IDENTIFIED BY PASSWORD \'', authentication_string, '\';' ) AS CreateUserQueryFROM mysql.`user`WHERE `User` NOT IN ( 'root', 'mysql.session', 'mysql.sys' );# 这样拼接也可以 带有密码认证插件SELECT CONCAT( 'create user \'', user, '\'@\'', Host, '\' IDENTIFIED WITH \'', plugin, '\' AS \'', authentication_string, '\';' ) AS CreateUserQueryFROM mysql.`user`WHERE `User` NOT IN ( 'root', 'mysql.session', 'mysql.sys' );
4.拼接show grants语句查询用户权限
SELECT CONCAT( 'show grants for \'', user, '\'@\'', Host, '\';' ) AS ShowGrantsFROM 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 CreateDatabaseQueryFROM information_schema.SCHEMATAWHERE SCHEMA_NAME NOT IN ( 'information_schema', 'performance_schema', 'mysql', 'sys' );
6.拼接DROP table
SELECT CONCAT( 'DROP table ', TABLE_NAME, ';' )FROM information_schema.TABLESWHERE 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 datafreeFROM information_schema.tables tWHERE t.TABLE_SCHEMA = 'testdb' order by DATA_LENGTH desc;
10.查找无主键表及增加自增ID作为主键
# 查找出无主键的表SELECT t1.table_schema, t1.table_nameFROM information_schema.TABLES t1LEFT OUTER JOIN information_schema.TABLE_CONSTRAINTS t2 ON t1.table_schema = t2.TABLE_SCHEMAAND t1.table_name = t2.TABLE_NAMEAND t2.CONSTRAINT_NAME IN ('PRIMARY')WHERE t2.table_name IS NULLAND t1.TABLE_SCHEMA NOT IN ( 'information_schema', 'performance_schema', 'mysql', 'sys') ;# 拼接出增加自增ID作为主键的SQLSELECTCONCAT('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 t1LEFT OUTER JOIN information_schema.TABLE_CONSTRAINTS t2 ON t1.table_schema = t2.TABLE_SCHEMAAND t1.table_name = t2.TABLE_NAMEAND t2.CONSTRAINT_NAME IN ('PRIMARY')WHERE t2.table_name IS NULLAND 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]';# 拼接出大写表名改为小写的SQLSELECT 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]';
划线
评论
复制
发布于: 2020 年 08 月 24 日阅读数: 64
版权声明: 本文为 InfoQ 作者【Simon】的原创文章。
原文链接:【http://xie.infoq.cn/article/b72f9275c40bd258e64185f11】。文章转载请联系作者。
Simon
关注
MySQL技术学习者 2018.06.08 加入
公众号『MySQL技术』作者
评论