本篇文章給大家帶來的內容是關于mysql中常用的拼接語句的小結（代碼示例），有一定的參考價值，有需要的朋友可以參考一下，希望對你有所幫助。
前言：在mysql中 CONCAT ()函數用于將多個字符串連接成一個字符串，利用此函數我們可以將原來一步無法得到的sql拼接出來，在工作中也許會方便很多，下面主要介紹下幾個常用的場景。
注：適用于5.7版本 低版本可能稍許不同。
1.拼接查詢所有用戶
SELECT DISTINCT CONCAT( 'User: '', USER, ''@'', HOST, '';' ) AS QUERY FROM mysql.USER; # 當拼接字符串中出現'時 需使用轉義符
2.拼接DROP table
SELECT CONCAT( 'DROP table ', TABLE_NAME, ';' ) FROM information_schema. TABLES WHERE TABLE_SCHEMA = 'test';
3.拼接kill連接
SELECT concat('KILL ', id, ';') FROM information_schema. PROCESSLIST WHERE STATE LIKE 'Creating sort index';
4.拼接創建數據庫語句
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.拼接創建用戶的語句
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腳本也用到了拼接
#!/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.查找表碎片
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.查找無主鍵表 這個沒用到拼接 也分享出來吧
#查找某一個庫無主鍵表 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' ) ;
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
