Increment my id in my insert request

Question

hello, i have a table with some rows.

idClient, name, adress,country,...

i want to know how i can do an insert into this table with auto increment my idClient in my sql request..? Thx.

edit: i want do a request like this

insert into Client values((select max(idClient),...)

Answer 1

insert into Client values(NULL,"name","some address", "country")

Answer 2

ALTER TABLE Client CHANGE idClient
  idClient INT AUTO_INCREMENT PRIMARY KEY;

Then when you insert into the table, exclude the auto-incrementing primary key column from your insert:

INSERT INTO Client (name, address, country)
  VALUES ('name', 'address', 'country')...;

The new value of idClient will be generated.

This is the only way to do this safely if there are multiple instances of an application inserting rows at once. Using the MAX(idClient) method you describe will not work, because it's subject to race conditions.

Answer 3

Define the id column as AUTO_INCREMENT, then skip the assignment altogether:

CREATE TABLE clients (
id MEDIUMINT NOT NULL PRIMARY KEY,
name VARCHAR(255),
addr VARCHAR(255),
country VARCHAR(255),
PRIMARY KEY (id));

INSERT INTO clients
(name, addr, country)
VALUES
("name", "addr", "country");