巧用SQL拼接語句

前言：

在日常數據庫運維過程中，可能經常會用到各種拼接語句，巧用拼接SQL可以讓我們的工作方便很多，達到事半功倍的效果。本篇文章將會分享幾個日常會用到的SQL拼接案例，類似的SQL還可以舉一反三，探索出更多的可能性哦。

_注意_：適用於5.7版本，其他版本可能稍許不同。

1.CONCAT函數介紹

授人以魚不如授人以漁，拼接SQL主要用到的是CONCAT函數，我們先來介紹下該函數的用法。

CONCAT(s1,s2...,sn) 是合併字符串函數，返回結果為連接參數產生的字符串，參數可以是一個或多個，若有任何一個參數為 NULL，則返回值為 NULL。當拼接字符串中有 ' 時，要用 \\ 轉義，貌似用兩個單引號也行，不過還是推薦用 \\ 轉義，下面用幾個示例來了解下CONCAT函數的用法。

<code>mysql> SELECT CONCAT('MySQL','5.7'),CONCAT('MySQL',NULL),CONCAT('\\'MySQL\\'');
+-----------------------+----------------------+---------------------+
| CONCAT('MySQL','5.7') | CONCAT('MySQL',NULL) | CONCAT('\\'MySQL\\'') |
+-----------------------+----------------------+---------------------+
| MySQL5.7              | NULL                 | 'MySQL'             |
+-----------------------+----------------------+---------------------+/<code>

簡單介紹完CONCAT函數的使用方法後，下面分享幾個用到SQL拼接的場景，也許在你工作中會用到哦。

2.拼接查詢所有用戶

<code>SELECT DISTINCT
  CONCAT(
    'User: \\'',
    USER,
    '\\'@\\'',
    HOST,
    '\\';'
  ) AS QUERY
FROM
  mysql.USER;/<code>

3.拼接創建用戶的語句

<code># 有密碼字符串 在其他實例執行 可直接創建出與本實例相同密碼的用戶
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'
  );/<code>

4.拼接show grants語句查詢用戶權限

<code>SELECT
  CONCAT(
    'show grants for \\'',
    user,
    '\\'@\\'',
    Host,
    '\\';'
  ) AS ShowGrants
FROM
  mysql.`user`
WHERE
  `User` NOT IN (
    'root',
    'mysql.session',
    'mysql.sys'
  );/<code>

5.拼接創建數據庫語句

<code>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'
  );/<code>

6.拼接DROP table

<code>SELECT
  CONCAT(
    'DROP table ',
    TABLE_NAME,
    ';'
  )
FROM
  information_schema.TABLES
WHERE
  TABLE_SCHEMA = 'testdb' and TABLE_TYPE = 'BASE TABLE';/<code>

7.拼接kill連接

<code># 可以組合改變條件
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;/<code>

8.查看數據庫大小

<code># 查看整個實例大小
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;/<code>

9.查找表碎片

<code>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;/<code>

10.查找無主鍵表及增加自增ID作為主鍵

<code># 查找出無主鍵的表
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'
) ;/<code>

11.查找大寫表及轉為小寫表

<code># 若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]';/<code>

閱讀更多 MySQL技術 的文章

關鍵字: MySQL 語句 拼接