By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,705 Members | 1,393 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,705 IT Pros & Developers. It's quick & easy.

Semi-autoincrementing database column

P: n/a
Here's an interesting question I saw in pl.comp.lang.php. The person wants
to use an auto-incrementing column to create an unique id for each product
in the database. These products belongs to different categories. He wants
those in the same category to receive an id in a specific range (e.g. fruits
in 1000-1999, vegetation in 2000-2999). How can this be done in MySQL in a
race-hazard-free manner?

In MS SQL I could do something like this:

INSERT INTO products
SELECT COALESCE(MAX(product_id) + 1, 1000), '$name', $price
FROM products
WHERE id >= 1000 AND id <= 1999

Don't know if the same is possible in MySQL.
Jul 17 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
The obvious response is to use multiple different tables for each category.
Then each can auto-increment happily. But if multiple tables are not
desired, then you can still lock the table, do the insert, and then unlock
the table.

Chung Leong wrote:
Here's an interesting question I saw in pl.comp.lang.php. The person
wants to use an auto-incrementing column to create an unique id for
each product in the database. These products belongs to different
categories. He wants those in the same category to receive an id in a
specific range (e.g. fruits in 1000-1999, vegetation in 2000-2999).
How can this be done in MySQL in a race-hazard-free manner?

In MS SQL I could do something like this:

INSERT INTO products
SELECT COALESCE(MAX(product_id) + 1, 1000), '$name', $price
FROM products
WHERE id >= 1000 AND id <= 1999

Don't know if the same is possible in MySQL.

Jul 17 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.