MYSQL: populating table with id

There is a feedback table:

id | authorid | subject | text

I want to fill it like this:

INSERT INTO feedback SET id=id+1,authorid=1,subject=2,text=3
  1. Why doesn’t it work? id = id + 1always returns 1.
  2. If the table is empty id = id + 1will return NULL and the table will not be filled. How to solve this problem?

I would be very happy for a full request. Thanks in advance.

PS: A similar problem was described by here, but I need it without where=bob, tried to compose it myself, didn’t work.


Answer 1, authority 100%

ALTER TABLE `feedback` CHANGE `id` `id` INT( 11 ) NOT NULL AUTO_INCREMENT

I would say this will solve your problem.

For questions:
id = id + 1 always returns 1.// It’s simple. A string is created. Id, as an int, is assigned the value 0. 0+1 = 1. Naturally, if ID is NOT NULL.

If the table is empty…..How to solve this problem?// write a condition:

IF((select max(`id`) as `mid` from `feedback` where 1),id=mid+1,id=1)

But this is a perversion. Terrible perversion. Use auto increment. The above shows how it connects to an empty table.


Answer 2, authority 50%

Who canceled auto-increment in MySQL? Or do you need to manually monitor the id?

Added.

ALTER TABLE `feedback` CHANGE `id` `id` INT( 11 ) NOT NULL AUTO_INCREMENT

Here is the link without the bean

INSERT INTO users (id, authorid,subject,text) 
SELECT 1 + coalesce((SELECT max(user_id) FROM users ), 0), 1,2,3;

Answer 3

Ilyushka Ovchinnikov, try like this

$sql = "INSERT INTO feedback(id, authorid, subject, text) SELECT(SELECT MAX(id)+1 from feedback), 1, 'subject_text', 'blablabla'";
mysql_query($sql);

everything worked out fine for me and id increases by 1


Answer 4

With each insertion, the id was incremented by one


Answer 5

Use AUTO_INCREMENT on the id field and you’ll be happier!