我正在嘗試插入一些日期時間值，但我想出了一個無意義的資料截斷問題，我不知道如何解決以及是什么原因造成的。希望有人幫忙！
我有下表：
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;
當我嘗試使用 1948 年插入時間戳時，出現此錯誤：
SQL 錯誤 [1292] [22001]：資料截斷：日期時間值不正確：第 1 行的列“dt_nascimento”的“1948-07-19 00:00:00”
但是當我嘗試使用 1999 年插入時間戳時，它使用以下查詢成功插入：
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',
'[email protected]', '[email protected]' )
Mysql Server 8.0
uj5u.com熱心網友回復：
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-type-syntax.html說：
時間戳。范圍是 '1970-01-01 00:00:01.000000' UTC 到 '2038-01-19 03:14:07.999999' UTC。
換句話說，“1948-07-19 00:00:00”比 TIMESTAMP 可以存盤的最早日期早 21 年以上。
如果您想要超出此范圍的日期，則應使用 DATETIME。
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/532782.html