MySQL 巧用SQL拼接语句

前言：

在日常数据库运维过程中，可能经常会用到各种拼接语句，巧用拼接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
 WHEREUser NOT IN ('root','mysql.session','mysql.sys');
 # 这样拼接也可以 带有密码认证插件
 SELECT
 CONCAT('create user \'',user,'\'@\'',
 Host,'\' IDENTIFIED WITH \'',
 plugin,'\' AS \'',
 authentication_string,'\';') AS CreateUserQuery
 FROM
 mysql.user
 WHEREUser NOT IN ('root','mysql.session','mysql.sys');
复制

4.拼接show grants语句查询用户权限

 SELECT
 CONCAT('show grants for \'',user,'\'@\'',
 Host,'\';') AS ShowGrants
 FROM
 mysql.user
 WHEREUser 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]';
复制

配置文件

 [mysqld]
 user=mysql
 basedir=/data/app/mysql
 datadir=/data/3306/data
 socket=/tmp/mysql.sock
 #log_error=/tmp/mysql.log
 #default_storage_engine=InnoDB
 
 #开启日志
 server_id=6  #主机id 
 log_bin=/data/3306/logs/mysql-bin  #开关+文件路径+文件名浅醉
 
 
 
 
 
 
 #设置共享表空间2-3个，大小建议512M或者1G，最后一个定制为自动扩展。
 innodb_data_file_path=ibdata1:76M;ibdata2:12M;ibdata3:30M:autoextend
 
 ##------------------------------------------
 #打开独立undo模式，并设置undo的个数。
 #innodb_undo_tablespaces=3
 
 #undo日志的大小，默认1G。
 #innodb_max_undo_log_size=128M
 
 #开启undo自动回收的机制（undo_purge）。
 #innodb_undo_log_truncate=ON
 #触发自动回收的条件，单位是检测次数。
 #innodb_purge_rseg_truncate_frequency=32
 ##------------------------------------------------
 
 
 
 ##生产建议： 512M-4G  2-4组 记录数据页的变化。实现“前进”的功能
 innodb_log_file_size=100M
 innodb_log_files_in_group=3
 #
 ##用来缓冲、缓存，MySQL的数据页和索引页  物理内存的：50-80%。
 innodb_buffer_pool_size=256M
 ##来缓冲 redo log日志信息。和innodb_log_file_size有关，1-N倍
 innodb_log_buffer_size=33554432
 
 ##作用： 存储临时表。
 innodb_temp_data_file_path=ibtmp1:12M;ibtmp2:128M:autoextend:max:500M
 ##用来缓冲、缓存
 innodb_buffer_pool_size=256M
 ##用来缓冲 redo log日志信息。
 innodb_log_buffer_size=33554432
 
 autocommit=0
 #transaction_isolation=READ-UNCOMMITTED
 #transaction_isolation=READ-COMMITTED
 
 #错误日志
 log_error=/data/3306/logs/mysql.err
 
 #gtid_的开启
 gtid_mode=ON
 #强制GTID一致性
 enforce_gtid_consistency=ON
 #强制从库更新binlog
 log_slave_updates=ON
 
 #慢日志开启查询
 slow_query_log=1
 slow_query_log_file=/data/3306/logs/slow.log 
 long_query_time=0.1
 log_queries_not_using_indexes=1
 
 [mysql]
 socket=/tmp/mysql.sock
 
 [mysql]
 socket=/tmp/mysql.sock
 prompt=db01_5.7 [\d]>
 
 [client]
 socket=/tmp/mysql.sock
 
复制