Mysql插入查询返回错误1062(23000):关键“主”的重复输入“2147483647”

I've noticed an error during an insert query in my database.

在我的数据库中插入查询时，我注意到一个错误。

mysql> insert into users (name) values ('Gepp');

returned:

ERROR 1062 (23000): Duplicate entry '2147483647' for key 'PRIMARY'

错误1062(23000):重复输入“2147483647”

It's the first time I get this error maybe this suggests that some kind of limit has been reached. Anyway I've checked in other posts complaining for the same error and found out that triggers may be the problem. Unfortunately it's not the case:

这是我第一次得到这个误差也许这表明已经达到了某种极限。不管怎样，我在其他的帖子中检查了同样的错误，并发现触发可能是问题所在。不幸的是，事实并非如此:

mysql> SHOW triggers;
Empty set (0.00 sec)

EDIT The structure of my users table is shown below:

编辑用户表的结构如下图所示:

> *************************** 1. row ***************************
  Field: uid
   Type: int(11)
   Null: NO
    Key: PRI
Default: NULL
  Extra: auto_increment
*************************** 2. row ***************************
  Field: name
   Type: varchar(50)
   Null: NO
    Key: 
Default: NULL
  Extra: 
*************************** 3. row ***************************
  Field: email
   Type: varchar(100)
   Null: NO
    Key: UNI
Default: NULL
  Extra: 
*************************** 4. row ***************************
  Field: encrypted_password
   Type: varchar(80)
   Null: NO
    Key: 
Default: NULL
  Extra: 
*************************** 5. row ***************************
  Field: salt
   Type: varchar(10)
   Null: NO
    Key: 
Default: NULL
  Extra: 
*************************** 6. row ***************************
  Field: descrizione
   Type: varchar(600)
   Null: YES
    Key: 
Default: NULL
  Extra: 
*************************** 7. row ***************************
  Field: motto
   Type: varchar(100)
   Null: NO
    Key: 
Default: 
  Extra: 
*************************** 8. row ***************************
  Field: status
   Type: varchar(100)
   Null: NO
    Key: 
Default: Hey new gambler! Share your thoughts!
  Extra: 
*************************** 9. row ***************************
  Field: game
   Type: varchar(100)
   Null: NO
    Key: 
Default: 
  Extra: 
*************************** 10. row ***************************
  Field: pokeroom
   Type: varchar(100)
   Null: NO
    Key: 
Default: 
  Extra: 
*************************** 11. row ***************************
  Field: score
   Type: int(11)
   Null: NO
    Key: 
Default: 0
  Extra: 
*************************** 12. row ***************************
  Field: created_at
   Type: datetime
   Null: YES
    Key: 
Default: NULL
  Extra: 
*************************** 13. row ***************************
  Field: updated_at
   Type: datetime
   Null: YES
    Key: 
Default: NULL
  Extra: 
*************************** 14. row ***************************
  Field: photo
   Type: varchar(500)
   Null: NO
    Key: 
Default: 
  Extra: 
*************************** 15. row ***************************
  Field: panorama
   Type: varchar(500)
   Null: NO
    Key: 
Default: 
  Extra:

How can I solve this problem?

我该如何解决这个问题?

3 个解决方案

#1

Thank you guys for your help! There was a bad configuration of the table. The uid column had the primary key and the auto_increment attribute but in the project I'm working on users were created with a query like this:

谢谢你们的帮助!桌子的配置很糟糕。uid列有主键和auto_increment属性，但在项目中，我正在使用这样的查询创建用户:

INSERT INTO users(uid, name, email, encrypted_password, salt, created_at) VALUES('12342354355.54534543','bollo','sai','dsfsd','sdsdf','23')

The uid was generated by the PHP function uniqid("",true) and this caused the problem

uid是由PHP函数uniqid(“”，true)生成的，这导致了问题。

    select uid,id from users;
+------------+----+
| uid        | id |
+------------+----+
|        183 |  1 |
|       5224 |  2 |
|       5228 |  3 |
|      52288 |  4 |
|     515620 |  5 |
|     519030 |  6 |
|    5156147 |  8 |
|    5156151 |  9 |
|    5156205 | 10 |
|    5157726 | 11 |
|   52289002 | 12 |
|  515615576 | 13 |
| 2147483647 | 14 |
+------------+----+
15 rows in set (0.00 sec)

As you can see a new uid, created by a query like the one above, was always greater than the previous one. Probably the auto_increment accepted only uid value greater than the last value inserted. I have been lucky for 14 registrations and then the uid value exceeded the maximum allowed by the definition of the column and caused the error.

正如上面的查询所示，您可以看到一个新的uid，它总是大于前一个uid。可能auto_increment只接受大于最后一个值的uid值。我幸运地获得了14个注册，然后uid值超过了列定义所允许的最大值，并导致了错误。

I've solved the problem by removing the Primary_Key from the uid columm:

我通过删除uid列中的Primary_Key解决了这个问题:

alter table users drop primary key;

modified it again to remove the auto_increment attribute:

再次修改它以删除auto_increment属性:

alter table users modify uid varchar(40) not null unique;

and finally added a new column called id in order to track and count users registrations:

最后添加了一个名为id的新列，以便跟踪和计算用户注册:

alter table users add id int(11) not null auto_increment primary key;

In the end the error was caused by a bad organization of the database and the functions acting on it. My fault!

最后，该错误是由数据库的错误组织和作用于数据库的函数引起的。我的错!

#2

I was getting a very similar duplicate PRIMARY value for the index when running a large script to input 1,000 of rows.

在运行一个大型脚本输入1,000行代码时，我得到了一个非常类似的索引值。

I dropped the primary index key, then ran my script, then re-enabled my "id" column as primary and now everything works fine.

我删除了主索引键，然后运行我的脚本，然后重新启用我的“id”列作为主要内容，现在一切正常。

#3

-2

Recently I solved the similar issue, error code:1062 duplicate entry. From this page how to solve mysql error code:1062 duplicate key? I found that, this error is because of the primary key field data type reached its upper limit, also changing the data type from int to bigint may helpful but changing the data type is depending on your requirement. There is a workaround in that page, i think it will help you to understand this issue better, thank you.

最近我解决了类似的问题，错误代码:1062重复输入。从这个页面如何解决mysql错误代码:1062复制键?我发现，这个错误是由于主键字段数据类型达到了它的上限，也改变了从int到bigint的数据类型可能有用，但是改变数据类型取决于您的需求。在这一页有一个变通方法，我想它会帮助你更好地理解这个问题，谢谢。

#1