mysql 将A库的表名和表字段注释,迁移到另外一个库中,生成alter 语句

表备注

-- 获取用于添加表备注的 SQL 语句
SELECT 
    CONCAT('ALTER TABLE 目标数据库名.', TABLE_NAME, ' COMMENT = \'', TABLE_COMMENT, '\';') AS alter_table_comment_sql
FROM 
    INFORMATION_SCHEMA.TABLES
WHERE 
    TABLE_SCHEMA = '源数据库名'
    AND TABLE_COMMENT IS NOT NULL;

表字段备注

-- 获取用于添加字段备注的 SQL 语句
SELECT 
    CONCAT(
        'ALTER TABLE 目标数据库名.', 
        TABLE_NAME, 
        ' MODIFY COLUMN ', 
        COLUMN_NAME, 
        ' ', 
        COLUMN_TYPE, 
        CASE 
            WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' 
            ELSE '' 
        END, 
        CASE 
            WHEN COLUMN_DEFAULT IS NOT NULL THEN ' DEFAULT ' ELSE '' 
        END,
        IFNULL(COLUMN_DEFAULT, ''), 
        ' COMMENT \'', 
        COLUMN_COMMENT, 
        '\';'
    ) AS alter_column_comment_sql
FROM 
    INFORMATION_SCHEMA.COLUMNS
WHERE 
    TABLE_SCHEMA = '源数据库名'
    AND COLUMN_COMMENT IS NOT NULL;

 

posted @ 2024-09-19 17:26  陈彦斌  阅读(14)  评论(0编辑  收藏  举报