MySQL插入1948年timestamp字段出现数据截断错误的原因排查

问题场景 #

在MySQL Server 8.0环境下，向tb_cliente表插入年份为1948的日期值时触发数据截断错误，但插入1999年的日期值可正常执行。

表结构 #

CREATE TABLE tb_cliente ( 
    id bigint unsigned NOT NULL AUTO_INCREMENT, 
    st_servidor tinyint(1) NOT NULL, 
    nm_nome varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, 
    nm_nome_social varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    cd_matricula varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, 
    cd_siape varchar(255) CHARACTER SET utf8mb4 
            COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    dt_nascimento timestamp NOT NULL, 
    sexo varchar(1) COLLATE utf8mb4_unicode_ci NOT NULL, 
    nacionalidade varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, 
    ano_chegada_no_pais varchar(255) 
        COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    naturalidade varchar(255) CHARACTER SET utf8mb4 
        COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    uf_nascimento varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, 
    estado_civil varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, 
    sn_conjuge_servidor tinyint(1) DEFAULT NULL, 
    nm_conjuge varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    nm_pai varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    nm_mae varchar(255) CHARACTER SET utf8mb4 
        COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    raca varchar(255) CHARACTER SET utf8mb4 
        COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    cor_dos_olhos varchar(255) CHARACTER SET utf8mb4 
        COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    cor_dos_cabelos varchar(255) CHARACTER SET utf8mb4 
        COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    sn_alergico tinyint(1) DEFAULT NULL, 
    grau_de_instrucao varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    dt_conclusao timestamp NULL DEFAULT NULL, 
    altura decimal(8,2) DEFAULT NULL, 
    tipo_sanguineo varchar(255) CHARACTER SET utf8mb4 
        COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    sn_doador_de_orgaos tinyint(1) DEFAULT NULL, 
    nome_anterior varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    endereco varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    nr_cep varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    nr_cpf varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, 
    telefone varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    telefone_celular varchar(255) 
        COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    email_institucional varchar(255) 
        COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    email_externo varchar(255) 
        COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    email_comunicados varchar(255) 
        COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    endereco_correspondencia varchar(255) 
        COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    nr_cep_correspondencia varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, 
    created_at timestamp NULL DEFAULT NULL, 
    updated_at timestamp NULL DEFAULT NULL, 
    user_id bigint unsigned DEFAULT NULL, 
    PRIMARY KEY (id), 
    UNIQUE KEY tb_servidor_nr_cpf_unique (nr_cpf), 
    KEY tb_servidor_user_id_index (user_id), 
    CONSTRAINT tb_servidor_user_id_foreign FOREIGN KEY (user_id) 
        REFERENCES tb_users (id) ON DELETE CASCADE ON UPDATE CASCADE 
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

错误信息 #

SQL Error [1292] [22001]: Data truncation: Incorrect datetime value: '1948-07-19 00:00:00' for column 'dt_nascimento' at row 1

成功插入的示例语句 #

insert into tb_cliente (
            updated_at, created_at, st_servidor, 
            nm_nome, cd_matricula, dt_nascimento, 
            sexo, nacionalidade, uf_nascimento, 
            estado_civil, grau_de_instrucao, nr_cpf, 
            telefone, telefone_celular, email_institucional, email_externo) 
values ('2022-10-25 00:00:00', '2022-10-25 00:00:00', '1', 
        'User name 1', '000000', '1999-07-19 00:00:00','M', 
        '1', 'AC', 'CASADO','ENSINO' , '000000', '000000', '000000', 
        'USER@UOL.COM.BR', 'USER@UOL.COM.BR' )

原因分析 #

MySQL的timestamp类型有严格的取值范围限制：默认是**'1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC**。要插入的1948年日期早于这个范围的下限，因此触发数据截断错误；而1999年的日期在合法范围内，所以插入成功。

解决方法 #

方法一：修改字段类型为datetime（推荐） #

datetime类型的取值范围是**'1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'**，完全覆盖1948年的日期需求。执行以下SQL修改字段：

ALTER TABLE tb_cliente MODIFY COLUMN dt_nascimento datetime NOT NULL;
-- 如果dt_conclusao字段也需要存储早期日期，可一并修改
ALTER TABLE tb_cliente MODIFY COLUMN dt_conclusao datetime NULL DEFAULT NULL;

方法二：调整MySQL全局参数（不推荐） #

若必须使用timestamp类型，可修改explicit_defaults_for_timestamp参数并调整时区配置，确保转换后的UTC时间在合法范围内。但该方法会影响整个数据库的timestamp行为，可能引发其他兼容性问题，不建议用于存储早于1970年的日期。

内容的提问来源于stack exchange，提问作者fabionvs