Mysql 偷懒搞替换
程序员文章站
2022-07-07 18:27:59
...
-- mysql 查询数据库表名
-- 查询数据库中所有表名
select table_name from information_schema.tables where table_schema='ems' and table_type='base table';
-- 批量改表名
SELECT
CONCAT(
'ALTER TABLE ',
table_name,
' RENAME TO ',
substring_index(table_name,'_tanli',1),
';'
) as tableSql
FROM
information_schema. TABLES
WHERE
table_schema = 'ems'
AND table_type = 'base table'
AND TABLE_NAME LIKE '%_tanli%';
-- 备份表
SELECT
CONCAT(
'ALTER TABLE ',
substring_index(table_name,'_tanli',1),
' RENAME TO ',
CONCAT(substring_index(table_name,'_tanli',1),'_TLBack'),
';'
) as tableSql
FROM
information_schema. TABLES
WHERE
table_schema = 'ems'
AND table_type = 'base table'
AND TABLE_NAME LIKE '%_tanli%';
-- 删除备份表 (多表)
SELECT
CONCAT(
'drop table ',
CONCAT(substring_index(table_name,'_tanli',1),'_TLBack'),
';'
) as tableSql
FROM
information_schema. TABLES
WHERE
table_schema = 'ems'
AND table_type = 'base table'
AND TABLE_NAME LIKE '%_tanli%';
-- 少量表可以操作
SELECT
CONCAT('Drop table ',
group_concat(CONCAT(substring_index(table_name,'_tanli',1),'_TLBack')),
';')
FROM
information_schema. TABLES
WHERE
table_schema = 'ems'
AND table_type = 'base table'
AND TABLE_NAME LIKE '%_tanli%';
-- 查询指定数据库中指定表的所有字段名column_name
select column_name from information_schema.columns where table_schema='csdb' and table_name='users'
SELECT
column_name
FROM
information_schema. COLUMNS
WHERE
table_schema = 'ems'
AND table_name = 'psc_app';
-- 查询数据库中所有表名
select table_name from information_schema.tables where table_schema='ems' and table_type='base table';
-- 批量改表名
SELECT
CONCAT(
'ALTER TABLE ',
table_name,
' RENAME TO ',
substring_index(table_name,'_tanli',1),
';'
) as tableSql
FROM
information_schema. TABLES
WHERE
table_schema = 'ems'
AND table_type = 'base table'
AND TABLE_NAME LIKE '%_tanli%';
-- 备份表
SELECT
CONCAT(
'ALTER TABLE ',
substring_index(table_name,'_tanli',1),
' RENAME TO ',
CONCAT(substring_index(table_name,'_tanli',1),'_TLBack'),
';'
) as tableSql
FROM
information_schema. TABLES
WHERE
table_schema = 'ems'
AND table_type = 'base table'
AND TABLE_NAME LIKE '%_tanli%';
-- 删除备份表 (多表)
SELECT
CONCAT(
'drop table ',
CONCAT(substring_index(table_name,'_tanli',1),'_TLBack'),
';'
) as tableSql
FROM
information_schema. TABLES
WHERE
table_schema = 'ems'
AND table_type = 'base table'
AND TABLE_NAME LIKE '%_tanli%';
-- 少量表可以操作
SELECT
CONCAT('Drop table ',
group_concat(CONCAT(substring_index(table_name,'_tanli',1),'_TLBack')),
';')
FROM
information_schema. TABLES
WHERE
table_schema = 'ems'
AND table_type = 'base table'
AND TABLE_NAME LIKE '%_tanli%';
-- 查询指定数据库中指定表的所有字段名column_name
select column_name from information_schema.columns where table_schema='csdb' and table_name='users'
SELECT
column_name
FROM
information_schema. COLUMNS
WHERE
table_schema = 'ems'
AND table_name = 'psc_app';
上一篇: MYSQL创建用户授权操作
下一篇: String和ByteBuffer互转
推荐阅读
-
mysql基于正则实现模糊替换字符串的方法分析
-
mysql替换表中的字符串的sql语句
-
mysql update正则替换sql语句
-
定时导出mysql本地数据替换远程数据库数据脚本分享
-
在MySQL中用正则表达式替换数据库中的内容的方法
-
WSO2 API Manager 替换mysql作为数据库,解决AuthorizationUtils Could not set authorizations for the root问题
-
MySQL替换函数replace()替换指定字段中的所有字符子串为其他字符串
-
Mysql replace替换用法
-
关于避免MySQL替换逻辑SQL的坑爹操作详解
-
Mysql 偷懒搞替换