How to change primary key values in MySQL?

To change values of existing integer field (`table` is the table name, and "id" is the column name), you may use the following update SQL command:

update `table` set id=id+10000;

Let's say that you have an e-commerce website, and you want your order number to begin at 10001. But, you already have a handful of orders already so you'll just update the existing values.

If you're updating your primary key values, MySQL will automatically adjust the auto_increment value to begin after the largest number stored in the database.

To reset your auto-increment value in MySQL database:

alter table `table` auto_increment = 1;

Notice, we are setting the auto_increment value to 1. By assigning 1, MySQL will automatically find the largest number and increment by 1. You may verify this by "SHOW CREATE TABLE" MySQL command.

% mysql
mysql> show create table `table`


CREATE TABLE `table` (
  `id` int(3) unsigned NOT NULL AUTO_INCREMENT,
  ...
  PRIMARY KEY (`id`),
) ENGINE=MyISAM AUTO_INCREMENT=6132 DEFAULT CHARSET=latin1

You may find other MySQL functions and operators useful. For example, if you wish to replace incorrectly added backslashes from a table column:

UPDATE table_name SET col = REPLACE(col, '\\', '')

References

• MySQL functions and operators