PHPMyAdmin/MySql-添加ID字段并自动填充ID号 [英] PHPMyAdmin / MySql - Add ID field and autopopulate ID numbers

问题描述

我有一个非常大的数据库表-近2000万条记录.

I have an extremely large database table - nearly 20 million records.

记录没有唯一的ID号.因此，我插入了新字段.

The records do not have a unique ID number. So, I've inserted the new field.

现在，我想用ID号填充它，并以1，首先从10,000,001开始.

Now, I would like to populate it with ID numbers, increasing by 1, starting with the first ID number being 10,000,001.

仅供参考-我正在本地计算机上使用WAMP，我已经拨动了所有最大时间(最长5000秒)，并拨通了php.ini和mysql.ini中的其他几个变量，以便首先进行上传(花了十多个小时！！

FYI - I am using WAMP on a local machine and I've dialed all my max times upto 5000 seconds and dialed up several other variables in php.ini and mysql.ini in order to do the upload in the first place (which took more than 10 hours!!).

过去，或者与其他数据库一起，我可能已经将数据导出到excel中，然后生成了一些文本以粘贴回phpmyadmin中以更新记录.当处理5K甚至10万条记录时，这很好，但是对于2000万条记录来说，这似乎是无法解决的.

In the past, or with other DB's, I might have exported the data into excel and then whipped up some text to paste back into phpmyadmin to UPDATE the records. This is fine when working with 5K records, or even 100K records, but this seems unmanagable with 20 million records.

提前谢谢！

推荐答案

只需在SQL选项卡中依次运行这两个查询即可:

Just run these two queries one after the other in the SQL tab:

ALTER TABLE mytable AUTO_INCREMENT=10000001;

ALTER TABLE mytable ADD `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

然后，MySQL将创建id字段，并从10000001开始依次进行填充.

MySQL will then create the id field and fill it in sequentially starting at 10000001.

这篇关于PHPMyAdmin/MySql-添加ID字段并自动填充ID号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！