mysql数据库拼接语句_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 \'',
简单的mysql语句
user,
'\'@\'',
Host,
'\''
' IDENTIFIED BY PASSWORD \'', authentication_string,
'\';'
) AS CreateUserQuery
FROM
mysql.`user`
`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
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
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 常⽤的拼接语句汇总的详细内容，更多关于MySQL 拼接语句的资料请关注我们其它相关⽂章！本⽂标题: MySQL 常⽤的拼接语句汇总

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。