mysql获取每个表的每个字段的长度

SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    COLUMN_NAME,
    DATA_TYPE,
    (
        CASE
            WHEN DATA_TYPE IN ('tinyint') THEN 1
            WHEN DATA_TYPE IN ('smallint') THEN 2
            WHEN DATA_TYPE IN ('mediumint') THEN 3
            WHEN DATA_TYPE IN ('int') THEN 4
            WHEN DATA_TYPE IN ('bigint') THEN 8
            WHEN DATA_TYPE IN ('float') THEN 4
            WHEN DATA_TYPE IN ('double') THEN 8
            WHEN DATA_TYPE = 'decimal' THEN NUMERIC_PRECISION + NUMERIC_SCALE
            WHEN DATA_TYPE IN ('char', 'varchar', 'binary', 'varbinary') THEN
                IF(DATA_TYPE IN ('char', 'binary'), CHARACTER_MAXIMUM_LENGTH, 
                   CHARACTER_MAXIMUM_LENGTH * 4 + IF(CHARACTER_MAXIMUM_LENGTH <= 255, 1, 2)) -- 对于字符串类型，假设UTF-8编码，实际长度需根据字符集确定
            WHEN DATA_TYPE IN ('date', 'time') THEN 3
            WHEN DATA_TYPE IN ('datetime', 'timestamp') THEN 5 + IFNULL(DATETIME_PRECISION, 0)
            WHEN DATA_TYPE = 'year' THEN 1
            ELSE '未知'
        END
    ) AS APPROX_FIELD_SIZE
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema')   -- 排除系统表
        AND TABLE_NAME = 'your_table_name' -- 需要查询的表名
ORDER BY
    TABLE_SCHEMA,
    TABLE_NAME,
    COLUMN_NAME;