mysql Out of range value for column

今天又遇到这个情况，insert 失败：

mysql> insert into info(ip,uid,mac_number) values (3232261220,'00E511122345','4567');        
ERROR 1062 (23000): Duplicate entry '2147483647-00E511122345-4567' for key 'PRIMARY'
mysql> insert into info(ip,uid,mac_number) values (3232261221,'00E511122345','4567');
ERROR 1062 (23000): Duplicate entry '2147483647-00E511122345-4567' for key 'PRIMARY
mysql> show warnings;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 'ip' at row 1 |
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table info\G;
*************************** 1. row ***************************
       Table: info
Create Table: CREATE TABLE `info` (
  `ip` int(10) NOT NULL,
  `uid` char(12) NOT NULL,
  `mac_number` int(10) NOT NULL,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `3g_number` int(20) DEFAULT NULL,
  PRIMARY KEY (`ip`,`uid`,`mac_number`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR:
No query specified

 

 

达到整数的最大值，修改这个字段为 bigint 或者 unsigned int ：

 

mysql> select * from info;
+------------+--------------+------------+---------------------+-----------+
| ip         | uid          | mac_number | create_time         | 3g_number |
+------------+--------------+------------+---------------------+-----------+
| 2147483647 | 00E511122345 |       4567 | 2013-08-20 10:18:46 |      NULL |
+------------+--------------+------------+---------------------+-----------+
1 row in set (0.00 sec)

mysql> alter table info change `ip` `ip` bigint(15) NOT NULL;                                                                                                                             
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into info(ip,uid,mac_number) values (inet_aton('172.168.100.100'),'00E511122345','4567');                                 
Query OK, 1 row affected (0.00 sec)

mysql> insert into info(ip,uid,mac_number) values (inet_aton('172.168.100.100'),'00E511122345','4567');
ERROR 1062 (23000): Duplicate entry '2896716900-00E511122345-4567' for key 'PRIMARY'
mysql> insert into info(ip,uid,mac_number) values (inet_aton('172.168.100.101'),'00E511122345','4567');
Query OK, 1 row affected (0.00 sec)

mysql>