Mysql 批量修改字段类型

需求:
将数据库所有 datetime 类型字段改为 datetime(6)

mysql 有一个数据库 information_schema 其中 COLUMNS 表保存了所有数据、所有表的字段信息

mysql> show full columns from information_schema.COLUMNS;
+--------------------------+----------------------------+-----------------+------+-----+---------+-------+------------+---------+
| Field                    | Type                       | Collation       | Null | Key | Default | Extra | Privileges | Comment |
+--------------------------+----------------------------+-----------------+------+-----+---------+-------+------------+---------+
| TABLE_CATALOG            | varchar(64)                | utf8_tolower_ci | YES  |     | NULL    |       | select     |         |
| TABLE_SCHEMA             | varchar(64)                | utf8_tolower_ci | YES  |     | NULL    |       | select     |         |
| TABLE_NAME               | varchar(64)                | utf8_tolower_ci | YES  |     | NULL    |       | select     |         |
| COLUMN_NAME              | varchar(64)                | utf8_tolower_ci | YES  |     | NULL    |       | select     |         |
| ORDINAL_POSITION         | int unsigned               | NULL            | NO   |     | NULL    |       | select     |         |
| COLUMN_DEFAULT           | text                       | utf8_bin        | YES  |     | NULL    |       | select     |         |
| IS_NULLABLE              | varchar(3)                 | utf8_general_ci | NO   |     |         |       | select     |         |
| DATA_TYPE                | longtext                   | utf8_bin        | YES  |     | NULL    |       | select     |         |
| CHARACTER_MAXIMUM_LENGTH | bigint                     | NULL            | YES  |     | NULL    |       | select     |         |
| CHARACTER_OCTET_LENGTH   | bigint                     | NULL            | YES  |     | NULL    |       | select     |         |
| NUMERIC_PRECISION        | bigint unsigned            | NULL            | YES  |     | NULL    |       | select     |         |
| NUMERIC_SCALE            | bigint unsigned            | NULL            | YES  |     | NULL    |       | select     |         |
| DATETIME_PRECISION       | int unsigned               | NULL            | YES  |     | NULL    |       | select     |         |
| CHARACTER_SET_NAME       | varchar(64)                | utf8_general_ci | YES  |     | NULL    |       | select     |         |
| COLLATION_NAME           | varchar(64)                | utf8_general_ci | YES  |     | NULL    |       | select     |         |
| COLUMN_TYPE              | mediumtext                 | utf8_bin        | NO   |     | NULL    |       | select     |         |
| COLUMN_KEY               | enum('','PRI','UNI','MUL') | utf8_bin        | NO   |     | NULL    |       | select     |         |
| EXTRA                    | varchar(256)               | utf8_general_ci | YES  |     | NULL    |       | select     |         |
| PRIVILEGES               | varchar(154)               | utf8_general_ci | YES  |     | NULL    |       | select     |         |
| COLUMN_COMMENT           | text                       | utf8_bin        | NO   |     | NULL    |       | select     |         |
| GENERATION_EXPRESSION    | longtext                   | utf8_bin        | NO   |     | NULL    |       | select     |         |
| SRS_ID                   | int unsigned               | NULL            | YES  |     | NULL    |       | select     |         |
+--------------------------+----------------------------+-----------------+------+-----+---------+-------+------------+---------+
22 rows in set (0.02 sec)

生成所有字段更改的sql：

SELECT
    CONCAT(
        'ALTER TABLE ',
        TABLE_NAME,
        ' MODIFY COLUMN ',
        COLUMN_NAME,
        ' datetime(6) ',
    IF
        ( IS_NULLABLE = 'YES', 'NULL', 'NOT NULL' ),
    IF
        ( COLUMN_DEFAULT IS NULL, '', CONCAT( ' DEFAULT ', COLUMN_DEFAULT ) ),
    IF
        (
            COLUMN_COMMENT = '',
            '',
        CONCAT( ' COMMENT \'', COLUMN_COMMENT, '\'' )),
        ' ;' 
) 
FROM
    information_schema.COLUMNS 
WHERE
    TABLE_SCHEMA = 'my_database_name' -- 数据库名称

    AND COLUMN_TYPE = 'datetime';

结果如下：

ALTER TABLE notifications MODIFY COLUMN created_at datetime(6) NOT NULL ;
ALTER TABLE notifications MODIFY COLUMN published_at datetime(6) NOT NULL COMMENT '通知发布时间' ;
ALTER TABLE notifications MODIFY COLUMN updated_at datetime(6) NOT NULL ;
ALTER TABLE operation_histories MODIFY COLUMN created_at datetime(6) NOT NULL ;
ALTER TABLE operation_histories MODIFY COLUMN updated_at datetime(6) NOT NULL ;
ALTER TABLE pay_trades MODIFY COLUMN created_at datetime(6) NOT NULL ;
ALTER TABLE pay_trades MODIFY COLUMN updated_at datetime(6) NOT NULL ;
ALTER TABLE product_price_histories MODIFY COLUMN created_at datetime(6) NOT NULL ;
ALTER TABLE product_price_histories MODIFY COLUMN effected_at datetime(6) NOT NULL COMMENT '生效时间' ;
ALTER TABLE product_price_histories MODIFY COLUMN expired_at datetime(6) NULL COMMENT '失效时间' ;
ALTER TABLE product_price_histories MODIFY COLUMN updated_at datetime(6) NOT NULL ;

更改字段值精度

例如:

update products set expiration_time = DATE_FORMAT(expiration_time, '%Y-%m-%d %H:%i:%s.999999');