Menu
Let's say I have a table Order with these fields:
Initially Orders are created with payment_date and invoice_number with NULL values, so I could have these rows:
ID | payment_date | invoice_number
1 | NULL | NULL
2 | 03/10/11 | 1
3 | 03/14/11 | 2
4 | NULL | NULL
5 | NULL | NULL
What I wanna do is when I update a row to fill the payment_date I need to update the invoice_number with the first available invoice_number. For instance, if I update the fifth row, the invoice_number would be '3'.
A first approach would be to SELECT the MAX() value for invoice_number and then add 1 to that value, but that would have concurrency problems, right?.
What do you think would be the best solution?
Thanks in advance!
952
You don't have to specify NOT NULL on the column definition with AUTO_INCREMENT . You can leave it off, and MySQL will make the column NOT NULL . And if you specify NULL in place of NOT NULL , MySQL will accept the syntax, but it will ignore that, and make the column NOT NULL anyway.
In MySQL, a NULL value means unknown. A NULL value is different from zero ( 0 ) or an empty string '' . A NULL value is not equal to anything, even itself. If you compare a NULL value with another NULL value or any other value, the result is NULL because the value of each NULL value is unknown.
When we insert NULL value to AUTO_INCREMENT column, MySQL will return sequence number.
In MySQL, the syntax to change the starting value for an AUTO_INCREMENT column using the ALTER TABLE statement is: ALTER TABLE table_name AUTO_INCREMENT = start_value; table_name.
If you want to avoid concurrency problems, you can do this in a transaction or a trigger.
And if you do something like this :
update table
set
column = 'something'
column = (select max(id) + 1 from table)
where id = 123
You won't have concurrency problem either. When you're doing this update, the table is locked, so nobody else can update it while the query is running.
87
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
