欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Mysql 偷懒搞替换

程序员文章站 2022-07-07 18:26:47
...
-- 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';



相关标签: mysql