dsxpt62448 2011-09-08 20:31
浏览 282
已采纳

PHPMyAdmin / MySql - 添加ID字段和自动填充ID号

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

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

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

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!!).

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.

Thanks in advance!!

  • 写回答

3条回答 默认 最新

  • douzhu5900 2011-09-08 20:44
    关注

    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 will then create the id field and fill it in sequentially starting at 10000001.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)

报告相同问题?

悬赏问题

  • ¥15 flink cdc无法实时同步mysql数据
  • ¥100 有人会搭建GPT-J-6B框架吗?有偿
  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名