前言：在MySQL中 CONCAT ()函數(shù)用于將多個(gè)字符串連接成一個(gè)字符串，利用此函數(shù)我們可以將原來(lái)一步無(wú)法得到的sql拼接出來(lái)，在工作中也許會(huì)方便很多，下面主要介紹下幾個(gè)常用的場(chǎng)景。

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

1.拼接查詢所有用戶

SELECT DISTINCT CONCAT( ’User: ’’, USER, ’’@’’, HOST, ’’;’ ) AS QUERYFROM mysql.USER;# 當(dāng)拼接字符串中出現(xiàn)’時(shí) 需使用轉(zhuǎn)義符

2.拼接DROP table

SELECT CONCAT( ’DROP table ’, TABLE_NAME, ’;’ )FROM information_schema. TABLESWHERE TABLE_SCHEMA = ’test’;

3.拼接kill連接

SELECT concat(’KILL ’, id, ’;’)FROM information_schema. PROCESSLISTWHERE STATE LIKE ’Creating sort index’;

4.拼接創(chuàng)建數(shù)據(jù)庫(kù)語(yǔ)句

SELECT CONCAT( ’create database ’, ’`’, SCHEMA_NAME, ’`’, ’ DEFAULT CHARACTER SET ’, DEFAULT_CHARACTER_SET_NAME, ’;’ ) AS CreateDatabaseQueryFROM information_schema.SCHEMATAWHERE SCHEMA_NAME NOT IN ( ’information_schema’, ’performance_schema’, ’mysql’, ’sys’ );

5.拼接創(chuàng)建用戶的語(yǔ)句

SELECT CONCAT( ’create user ’’, user, ’’@’’, Host, ’’’ ’ IDENTIFIED BY PASSWORD ’’, authentication_string, ’’;’ ) AS CreateUserQueryFROM mysql.`user`WHERE `User` NOT IN ( ’root’, ’mysql.session’, ’mysql.sys’ );#有密碼字符串哦 在其他實(shí)例執(zhí)行 可直接創(chuàng)建出與本實(shí)例相同密碼的用戶

6.導(dǎo)出權(quán)限腳本 這個(gè)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.sqlecho 'flush privileges;' >> /tmp/grants.sql

7.查找表碎片

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 = ’test’ order by DATA_LENGTH desc;

8.查找無(wú)主鍵表 這個(gè)沒(méi)用到拼接 也分享出來(lái)吧

#查找某一個(gè)庫(kù)無(wú)主鍵表SELECTtable_schema,table_nameFROM information_schema.TABLESWHERE 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’);#查找除系統(tǒng)庫(kù)外 無(wú)主鍵表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’) ;

以上就是MySQL 常用的拼接語(yǔ)句匯總的詳細(xì)內(nèi)容，更多關(guān)于MySQL 拼接語(yǔ)句的資料請(qǐng)關(guān)注好吧啦網(wǎng)其它相關(guān)文章！