navicat设置decimal默认值失效

navicat有个很坑的点，好像也不只是它，就是在设置字段类型为decimal的时候，设置的默认值不生效，包括结构同步也不会同步这个的默认值，自己一个一个的找实在太麻烦了，索性利用 mysql的information_schema存储了数据的元信息，来自动拼接sql自动设置默认值

SELECT
        CONCAT("ALTER TABLE ",TABLE_NAME," MODIFY COLUMN ",COLUMN_NAME," ",COLUMN_TYPE,(case when IS_NULLABLE = "NO" THEN " NOT NULL" ELSE "" END)," DEFAULT 0 COMMENT '",COLUMN_COMMENT,"';") AS 'sql',
    TABLE_SCHEMA AS '库名',
    TABLE_NAME AS '表名',
    COLUMN_NAME AS '列名',
    ORDINAL_POSITION AS '列的排列顺序',
    COLUMN_DEFAULT AS '默认值',
    IS_NULLABLE AS '是否为空',
    DATA_TYPE AS '数据类型',
    CHARACTER_MAXIMUM_LENGTH AS '字符最大长度',
    NUMERIC_PRECISION AS '数值精度(最大位数)',
    NUMERIC_SCALE AS '小数精度',
    COLUMN_TYPE AS '列类型',
    COLUMN_KEY 'KEY',
    EXTRA AS '额外说明',
    COLUMN_COMMENT AS '注释'
FROM
    information_schema.`COLUMNS`
WHERE
    TABLE_SCHEMA = 'zxcrm-demo'
    AND DATA_TYPE = 'decimal'
    AND COLUMN_DEFAULT IS NULL and IS_NULLABLE = "NO";

这个sql就是修改表结构的sql语句了，复制出来直接运行就好了

navicat设置decimal默认值失效

张书贤

标签