前言：在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 QUERY
FROM
mysql.USER;
# 當(dāng)拼接字符串中出現(xiàn)'時(shí) 需使用\轉(zhuǎn)義符
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.拼接創(chuàng)建數(shù)據(jù)庫(kù)語(yǔ)句
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.拼接創(chuàng)建用戶的語(yǔ)句
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'
);
#有密碼字符串哦 在其他實(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.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.查找無(wú)主鍵表 這個(gè)沒(méi)用到拼接 也分享出來(lái)吧
#查找某一個(gè)庫(kù)無(wú)主鍵表
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'
);
#查找除系統(tǒng)庫(kù)外 無(wú)主鍵表
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 常用的拼接語(yǔ)句匯總的詳細(xì)內(nèi)容，更多關(guān)于MySQL 拼接語(yǔ)句的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！
您可能感興趣的文章:- MySQL使用show status查看MySQL服務(wù)器狀態(tài)信息
- Mysql中 show table status 獲取表信息的方法
- mysql建表常用的sql語(yǔ)句匯總
- 記一次MySQL更新語(yǔ)句update的踩坑
- Navicat Premium操作MySQL數(shù)據(jù)庫(kù)(執(zhí)行sql語(yǔ)句)
- MySQL兩種刪除用戶語(yǔ)句的區(qū)別(delete user和drop user)
- MySQL 數(shù)據(jù)庫(kù) like 語(yǔ)句通配符模糊查詢小結(jié)
- mysql中寫判斷語(yǔ)句的方法總結(jié)
- MySQL 數(shù)據(jù)查重、去重的實(shí)現(xiàn)語(yǔ)句
- MySQL SHOW STATUS語(yǔ)句的使用
