巧用SQL拼接语句

Bertha 。 2023-07-24 08:57 220阅读 0赞

**前言：**

在日常数据库运维过程中，可能经常会用到各种拼接语句，巧用拼接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]';

![WeChat][]

[WeChat]: /images/20230528/c5f63db618b24fa5a4f606f7a4d0fef2.png