mysql 5.7 ID int unsigned主键超过最大值问题

java 入库报错

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Out of range value for column 'ID' at row 1

解决办法：将`ID` int unsigned 类型 改成 bigint unsigned

BIGINT UNSIGNED 是 MySQL 中一种无符号的整数数据类型，使用 8 个字节（64 位）存储。取值范围是从 0 到 18,446,744,073,709,551,615（2^64-1），即最大值为无符号 64 位整数的最大值。

因为原表中有大量数据，直接修改原表的表结构，会导致锁表，影响实时入库。所以这样做：

1、创建一张一样的表，表名不一样

create table obs_station_data_1 like obs_station_data;

2、查看新 创建的表，索引和分区是否也创建出来了。

desc obs_station_data_1;

show create table obs_station_data_1;

3、改名，将原表改名成old

 alter table obs_station_data rename obs_station_data_old;

4、给新表改名

alter table obs_station_data_1 rename obs_station_data;

5、修改表ID字段类型为 bigint unsigned

alter table obs_station_data modify column ID bigint unsigned not null  auto_increment;

6、将旧表数据同步到新表

 insert into obs_station_data(PROD_NAME, ELE_NAME, LON, LAT, DATA_TIME, STATION_ID_C, ALTI, WIN_S_SENSOR_HEIGHT, STATION_LEVL, OBS_VALUE, ANALY_01_VALUE, ANALY_02_VALUE, ANALY_03_VALUE, ANALY_04_VALUE, INSERT_TIME, STATION_TYPE, QC_CODE, AFTER_QC_CODE, STATION_NAME) SELECT PROD_NAME, ELE_NAME, LON, LAT, DATA_TIME, STATION_ID_C, ALTI, WIN_S_SENSOR_HEIGHT, STATION_LEVL, OBS_VALUE, ANALY_01_VALUE, ANALY_02_VALUE, ANALY_03_VALUE, ANALY_04_VALUE, INSERT_TIME, STATION_TYPE, QC_CODE, AFTER_QC_CODE, STATION_NAME FROM obs_station_data_old;

7、避免xshell连接断开，命令失效，可以将语句在后台执行

将语句写入文件 insert.sql

use 数据库名称;

 insert into obs_station_data(PROD_NAME, ELE_NAME, LON, LAT, DATA_TIME, STATION_ID_C, ALTI, WIN_S_SENSOR_HEIGHT, STATION_LEVL, OBS_VALUE, ANALY_01_VALUE, ANALY_02_VALUE, ANALY_03_VALUE, ANALY_04_VALUE, INSERT_TIME, STATION_TYPE, QC_CODE, AFTER_QC_CODE, STATION_NAME) SELECT PROD_NAME, ELE_NAME, LON, LAT, DATA_TIME, STATION_ID_C, ALTI, WIN_S_SENSOR_HEIGHT, STATION_LEVL, OBS_VALUE, ANALY_01_VALUE, ANALY_02_VALUE, ANALY_03_VALUE, ANALY_04_VALUE, INSERT_TIME, STATION_TYPE, QC_CODE, AFTER_QC_CODE, STATION_NAME FROM obs_station_data_old;

在xshell中后台执行

mysql -h localhost -uroot -p密码 < insert.sql &