mysql的批量修改主键/默认值的常用命令

最近sqlserver通过navicte迁移到mysql后，发现id自增丢失，字段默认值丢失，通过下面运行下面命令生成批量sql语句，运行后即可更改字段相关属性。

给所有表增加主键

SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY;') AS alter_statement
FROM information_schema.tables
WHERE table_schema = 'company' AND table_name NOT LIKE '_tmp_table';

给所有表增加自增

SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `id` INT NOT NULL AUTO_INCREMENT;') AS alter_statement
FROM information_schema.tables
WHERE table_schema = 'company'  -- 替换为你的数据库名
AND table_name NOT LIKE '_tmp_table';

 

给所有datetime字段添加默认值为当前时间

SELECT
    CONCAT('ALTER TABLE `', table_name, '` MODIFY COLUMN `', column_name, '` datetime DEFAULT CURRENT_TIMESTAMP;') AS `query`
FROM
    information_schema.COLUMNS
WHERE
    TABLE_SCHEMA = 'test' -- 替换为你的数据库名
    AND DATA_TYPE = 'datetime';

 

给给所有数值字段添加默认值0

SELECT
    CONCAT('ALTER TABLE `', table_name, '` MODIFY COLUMN `', column_name, '` ' , data_type, ' DEFAULT 0;') AS `query`
FROM
    information_schema.COLUMNS
WHERE
    TABLE_SCHEMA = 'test' -- 替换为你的数据库名
    AND DATA_TYPE in ('int','smallint','tinyint','decimal','float','double') and column_name<>'id';