MySQL 批量修改日期格式

1
SELECT
2
  CONCAT(
3
    'ALTER TABLE `',
4
    TABLE_NAME,
5
    '` MODIFY COLUMN `',
6
    COLUMN_NAME,
7
    '` datetime(3) ',
8
  IF
9
    ( IS_NULLABLE = 'NO', 'NOT NULL', 'NULL' ),
10
  IF
11
    ( COLUMN_DEFAULT = 'CURRENT_TIMESTAMP(4)', ' DEFAULT CURRENT_TIMESTAMP(3) ', ' ' ),
12
  IF
13
    ( EXTRA = 'DEFAULT_GENERATED on update CURRENT_TIMESTAMP(4)', ' ON UPDATE CURRENT_TIMESTAMP(3) ', ' ' ),
14
  IF
15
    (
16
      CHARACTER_LENGTH( COLUMN_COMMENT ) > 0,
17
      CONCAT( "COMMENT '", COLUMN_COMMENT, "'" ),
18
      ''
19
    ),
20
    ';'
21
),
22
t1.*
23
FROM
24
  `COLUMNS` t1
25
WHERE
26
  TABLE_SCHEMA = '目标库'
27
  AND DATA_TYPE = 'datetime'
28
  AND DATETIME_PRECISION = 4;