**前言：**在MySQL中 CONCAT ()函式用於將多個字串連線成一個字串，利用此函式我們可以將原來一步無法得到的sql拼接出來，在工作中也許會方便很多，下面主要介紹下幾個常用的場景。

*注：*適用於5.7版本 低版本可能稍許不同。

##### 1.拼接查詢所有使用者

```mysql
SELECT DISTINCT
	CONCAT(
		'User: \'',
		USER,
		'\'@\'',
		HOST,
		'\';'
	) AS QUERY
FROM
	mysql.USER;
# 當拼接字串中出現'時 需使用\轉義符
```

##### 2.拼接DROP table

```mysql
SELECT
	CONCAT(
		'DROP table ',
		TABLE_NAME,
		';'
	)
FROM
	information_schema. TABLES
WHERE
	TABLE_SCHEMA = 'test';
```

##### 3.拼接kill連線

```mysql
SELECT
	concat('KILL ', id, ';')
FROM
	information_schema. PROCESSLIST
WHERE
	STATE LIKE 'Creating sort index';
```

##### 4.拼接建立資料庫語句

```mysql
SELECT
	CONCAT(
		'create database ',
		'`',
    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'
	);
```

##### 5.拼接建立使用者的語句

```mysql
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'
	);
#有密碼字串哦 在其他例項執行 可直接建立出與本例項相同密碼的使用者
```

##### 6.匯出許可權指令碼 這個shell指令碼也用到了拼接

```shell
#!/bin/bash  
#Function export user privileges

pwd=yourpass  
expgrants()  
{  
  mysql -B -u'root' -p${pwd} -N $@ -e "SELECT CONCAT(  'SHOW GRANTS FOR ''', user, '''@''', host, ''';' ) AS query FROM mysql.user" | \
  mysql -u'root' -p${pwd} $@ | \
  sed 's/$GRANT .*$/\1;/;s/^$Grants for .*$/-- \1 /;/--/{x;p;x;}'  
}  
 
expgrants > /tmp/grants.sql
echo "flush privileges;" >> /tmp/grants.sql
```

##### 7.查詢表碎片

```mysql
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 = 'test' order by DATA_LENGTH desc;
```

##### 8.查詢無主鍵表 這個沒用到拼接 也分享出來吧

```mysql
#查詢某一個庫無主鍵表
SELECT
table_schema,
table_name
FROM
    information_schema.TABLES
WHERE
    table_schema = 'test'
AND TABLE_NAME NOT IN (
    SELECT
        table_name
    FROM
        information_schema.table_constraints t
    JOIN information_schema.key_column_usage k USING (
        constraint_name,
        table_schema,
        table_name
    )
    WHERE
        t.constraint_type = 'PRIMARY KEY'
    AND t.table_schema = 'test'
);

#查詢除系統庫外 無主鍵表
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'
) ;
```

【MySQL】常用拼接語句

相關文章