如何在MySQL中设置初始值和自动增量?

How do I set the initial value for an "id" column in a MySQL table that start from 1001?

如何为从1001开始的MySQL表中的“id”列设置初始值?

I want to do an insert "INSERT INTO users (name, email) VALUES ('{$name}', '{$email}')";

我想插入“插入到用户(名称、电子邮件)值(‘{$name}’、‘{$email}’)”;

Without specifying the initial value for the id column.

不指定id列的初始值。

8 个解决方案

#1

Use this:

用这个:

ALTER TABLE users AUTO_INCREMENT=1001;

or if you haven't already added an id column, also add it

如果您还没有添加id列，也可以添加它。

ALTER TABLE users ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ADD INDEX (id);

#2

MySQL - Setup an auto-incrementing primary key that starts at 1001:

Step 1, create your table:

步骤1，创建您的表:

create table penguins(
  my_id       int(16) auto_increment, 
  skipper     varchar(4000),
  PRIMARY KEY (my_id)
)

Step 2, set the start number for auto increment primary key:

步骤2，设置自动递增主键的起始号:

ALTER TABLE penguins AUTO_INCREMENT=1001;

Step 3, insert some rows:

步骤3，插入一些行:

insert into penguins (skipper) values("We need more power!");
insert into penguins (skipper) values("Time to fire up");
insert into penguins (skipper) values("kowalski's nuclear reactor.");

Step 4, interpret the output:

步骤4，解释输出:

select * from penguins

prints:

打印:

'1001', 'We need more power!'
'1002', 'Time to fire up'
'1003', 'kowalski\'s nuclear reactor'

#3

MySQL Workbench

MySQL工作台

If you want to avoid writing sql, you can also do it in MySQL Workbench by right clicking on the table, choose "Alter Table ..." in the menu.

如果您想避免编写sql，也可以在MySQL Workbench中通过右键单击该表，在菜单中选择“Alter table…”。

When the table structure view opens, go to tab "Options" (on the lower bottom of the view), and set "Auto Increment" field to the value of the next autoincrement number.

当表结构视图打开时，转到选项卡“Options”(在视图的底部)，并将“自动增量”字段设置为下一个自动增量数字的值。

Don't forget to hit "Apply" when you are done with all changes.

当您完成所有更改时，不要忘记点击“Apply”。

PhpMyAdmin:

PhpMyAdmin:

If you are using phpMyAdmin, you can click on the table in the lefthand navigation, go to the tab "Operations" and under Table Options change the AUTO_INCREMENT value and click OK.

如果您正在使用phpMyAdmin，可以单击左侧导航中的表，转到“Operations”选项卡，在“table Options”下更改AUTO_INCREMENT值并单击OK。

#4

First you need to add column for auto increment

首先需要为自动增量添加列

alter table users add column id int(5) NOT NULL AUTO_INCREMENT FIRST

This query for add column at first. Now you have to reset auto increment initial value. So use this query

这个查询首先添加列。现在你需要重置自动增量初始值。所以用这个查询

alter table users AUTO_INCREMENT=1001

Now your table started with 1001

现在您的表以1001开始。

#5

Also , in PHPMyAdmin , you can select table from left side(list of tables) then do this by going there.
Operations Tab->Table Options->AUTO_INCREMENT.

Now, Set your values and then press Go under the Table Options Box.

另外，在PHPMyAdmin中，您可以从左侧(表列表)中选择table，然后到那里执行此操作。操作选项卡- >选项- > AUTO_INCREMENT表。现在，设置您的值，然后按下表选项框。

#6

For this you have to set AUTO_INCREMENT value

为此，必须设置AUTO_INCREMENT值。

ALTER TABLE tablename AUTO_INCREMENT = <INITIAL_VALUE>

Example

例子

ALTER TABLE tablename AUTO_INCREMENT = 101

#7

ALTER TABLE users AUTO_INCREMENT=1001;

ALTER TABLE用户AUTO_INCREMENT = 1001;

#8

You could also set it in the create table statement.

您还可以在create table语句中设置它。

`CREATE TABLE(...) AUTO_INCREMENT=1000`