CODEWITHSUNDEEP
mysqlsqltriggers
user1046065
user1046065

Reputation: 5

generate id number mysql

i want to generate a id number for my user table. id number is unique index.

here my trigger

USE `schema_epolling`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` TRIGGER `tbl_user_BINS` BEFORE INSERT ON `tbl_user` 
FOR EACH ROW
BEGIN
SET NEW.id_number = CONCAT(DATE_FORMAT(NOW(),'%y'),LPAD((SELECT auto_increment FROM   
information_schema.tables WHERE table_schema = 'schema_epolling' AND table_name =
'tbl_user'),6,0));
END

it works if i insert one by one .. or may 5 rows at a time. but if i insert a bulk rows.. an error occured.

 id number

heres the code i use for inserting bulk rows from another schema/table:

INSERT INTO schema_epolling.tbl_user (last_name, first_name)
SELECT last_name, first_name
FROM schema_nc.tbl_person

heres the error:

Error Code: 1062. Duplicate entry '14000004' for key 'id_number_UNIQUE'

Error Code: 1062. Duplicate entry '14000011' for key 'id_number_UNIQUE'

Error Code: 1062. Duplicate entry '14000018' for key 'id_number_UNIQUE'

Error Code: 1062. Duplicate entry '14000025' for key 'id_number_UNIQUE'

Error Code: 1062. Duplicate entry '14000032' for key 'id_number_UNIQUE'

if i use uuid() function it works fine. but i dont want uuid() its too long.

Upvotes: 0

Views: 1746

Answers (2)

MER
MER

Reputation: 1561

I'd like to add my two cents to expound on Bill Karwin's point. It's better that you don't generate a Unique ID by attempting to manually cobble one together.

The fact that your school produces an ID in that way does not mean that's the best way to do it (assuming that is what they are using that generated value for which I can't know without more information).

Your database work will be simpler and less error prone if you accept that the purpose for an ID field (or key) is to guarantee uniqueness in each row of data, not as a reference point to store certain pieces of human readable data in a central spot. This type of a ID/key is known as a surrogate key. If you'd like to read more about them here's a good article: http://en.wikipedia.org/wiki/Surrogate_key It's common for a surrogate key to also be the primary key of a table, (and when it's used in this way it can greatly simplify creating relationships between tables).

If you would like to add a secondary column that concatenates date values and other information because that's valuable for an application you are writing, or any other purpose you see fit, then create that as a separate column in your table.

Thinking of an ID column/key in this, fire & forget, way may simplify the concept enough that you may experience a number of benefits in your database creation efforts. As an example, should you require uniqueness between un-associated databases, you will more easily be able to stomach the use of a UUID. (Because you'll know it's purpose is merely to ensure uniqueness NOT to be useful to you in any other way.) Additionally, as you've found, taking the responsibility on yourself, instead of relying on the database, to produce a unique value adds time consuming complexity that can otherwise be avoided.

Hope this helps.

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562631

You don't want to generate id values that way.

The auto-increment value for the current INSERT is not generated yet at the time the BEFORE INSERT trigger executes.

Even if it were, the INFORMATION_SCHEMA would contain the maximum auto-increment value generate by any thread, not just the thread executing the trigger. So you would have a race condition that would easily conflict with other concurrent inserts and get the wrong value.

Also, querying INFORMATION_SCHEMA on every INSERT is likely to be a bottleneck for your performance.

In this case, to get the auto-increment value formatted with the two-digit year number prepended, you could advance the table's auto-increment value up to %y million, and then when we reach January 1 2015 you would ALTER TABLE to advance it again.

Re your comments:

The answer I gave above applies to how MySQL's auto-increment works. If you don't rely on auto-increment, you can generate the values by some other means.

  • Incrementing another one-row table as @Vatev suggests (though this creates a relatively long-lived lock on that table, which could be a bottleneck for your inserts).
  • Generating values in your application, based on an central, atomic id-generator like memcached. See other ideas here: Generate unique IDs in a distributed environment
  • Using UUID(). Yes, sorry, it's 32 characters long. Don't truncate it or you will use uniqueness.

But combining triggers with auto-increment in the way you show simply won't work.

Upvotes: 2

Related Questions