MySQL 线上表结构优化实战：从字段长度分析到批量 DDL 变更

DROP PROCEDURE IF EXISTS sp_analyze_field_lengths;
DELIMITER $$

CREATE PROCEDURE sp_analyze_field_lengths(
    IN p_schema      VARCHAR(64),  -- 目标数据库名，如 'demo_db'
    IN p_tables      TEXT,         -- 逗号分隔的表名，如 'table_a,table_b'
    IN p_gc_max_len  INT           -- GROUP_CONCAT 最大长度，建议 1048576（1 MB）
)
BEGIN
    DECLARE v_sql LONGTEXT DEFAULT '';

    -- 1. 调大 GROUP_CONCAT 上限，避免大表/宽表场景下拼接 SQL 被截断
    SET SESSION group_concat_max_len = IFNULL(p_gc_max_len, 1048576);

    -- 2. 从 information_schema 读取目标字段，动态拼接 UNION ALL 查询
    --    结果集结构：tbl | col | dtype | def_len | max_len | avg_len
    SELECT GROUP_CONCAT(
        CONCAT(
            'SELECT ''', TABLE_NAME,  ''' AS `tbl`, ',
                   '''', COLUMN_NAME, ''' AS `col`, ',
                   '''', DATA_TYPE,   ''' AS `dtype`, ',
            IFNULL(CHARACTER_MAXIMUM_LENGTH, 'NULL'), ' AS `def_len`, ',
            'MAX(LENGTH(`',         COLUMN_NAME, '`))           AS `max_len`, ',
            'ROUND(AVG(LENGTH(`',   COLUMN_NAME, '`)), 1)       AS `avg_len` ',
            'FROM `', p_schema, '`.`', TABLE_NAME, '`'
        )
        ORDER BY TABLE_NAME, ORDINAL_POSITION
        SEPARATOR ' UNION ALL '
    )
    INTO v_sql
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = p_schema
      AND FIND_IN_SET(TABLE_NAME, REPLACE(p_tables, ' ', ''))
      AND DATA_TYPE IN ('varchar', 'char', 'tinytext', 'text', 'mediumtext', 'longtext');

    -- 3. 加外层排序：def_len - max_len 差值最大的字段优先展示
    IF v_sql IS NULL OR v_sql = '' THEN
        SELECT '未找到目标字段，请检查 schema / 表名 / 字段类型' AS result;
    ELSE
        SET v_sql = CONCAT(
            'SELECT * FROM (', v_sql, ') t ',
            'ORDER BY (t.def_len - t.max_len) DESC, t.tbl, t.col'
        );
        SET @stmt = v_sql;
        PREPARE s FROM @stmt;
        EXECUTE s;
        DEALLOCATE PREPARE s;
    END IF;
END$$

DELIMITER ;

-- 分析 demo_db 库下 8 张业务表的 varchar/char 字段
CALL sp_analyze_field_lengths(
    'demo_db',
    'dispatch_defect,dispatch_task,dispatch_plan,dispatch_log,dispatch_config,dispatch_record,dispatch_alarm,dispatch_worker',
    1048576
);

-- 从 information_schema 读取字段元数据，生成完整 MODIFY COLUMN 语句
-- 使用前：将 CHARACTER_MAXIMUM_LENGTH 替换为阶段一分析得出的目标长度
SELECT
    c.TABLE_NAME                   AS `表名`,
    c.COLUMN_NAME                  AS `字段名`,
    c.CHARACTER_MAXIMUM_LENGTH     AS `当前长度`,
    CONCAT(
        'ALTER TABLE `', c.TABLE_NAME, '`',
        ' MODIFY COLUMN `', c.COLUMN_NAME, '` ',
        UPPER(c.DATA_TYPE), '(', c.CHARACTER_MAXIMUM_LENGTH, ')',
        -- 保留原有字符集与排序规则
        CASE c.CHARACTER_SET_NAME
            WHEN 'utf8mb4' THEN ' CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci'
            WHEN 'utf8'    THEN ' CHARACTER SET utf8 COLLATE utf8_general_ci'
            ELSE ''
        END,
        -- 保留 NULL / NOT NULL 约束
        IF(c.IS_NULLABLE = 'NO', ' NOT NULL', ' NULL'),
        -- 保留默认值（如有）；单引号内容需转义
        IF(c.COLUMN_DEFAULT IS NOT NULL,
            CONCAT(' DEFAULT ''', REPLACE(c.COLUMN_DEFAULT, '''', ''''''), ''''  ),
            ''),
        -- 保留或补充注释；空注释插入占位符提示补全
        ' COMMENT ''',
        IF(c.COLUMN_COMMENT IS NULL OR TRIM(c.COLUMN_COMMENT) = '',
            '【待补充注释】',
            REPLACE(c.COLUMN_COMMENT, '''', '''''')),
        ''';'
    ) AS `ALTER DDL`
FROM information_schema.COLUMNS c
WHERE c.TABLE_SCHEMA = 'demo_db'
  AND c.TABLE_NAME IN (
      'dispatch_defect',
      'dispatch_task',
      'dispatch_plan',
      'dispatch_log',
      'dispatch_config',
      'dispatch_record',
      'dispatch_alarm',
      'dispatch_worker'
  )
  AND c.DATA_TYPE IN ('varchar', 'char')
ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION;

ALTER TABLE `dispatch_defect` MODIFY COLUMN `remark` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '备注信息';
ALTER TABLE `dispatch_defect` MODIFY COLUMN `address` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '【待补充注释】';
ALTER TABLE `dispatch_defect` MODIFY COLUMN `handler` VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '处理人';
ALTER TABLE `dispatch_task` MODIFY COLUMN `task_name` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '任务名称';
ALTER TABLE `dispatch_task` MODIFY COLUMN `description` VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '任务描述';