0

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.

user1046065
  • 5
  • 7

2 Answers2

2

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.

Community
  • 1
  • 1
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • how? i have autonumber user_id in tbl_user. and i dont want to alter it again when it reach 2015. is there any solution for generating id number? – user1046065 May 27 '14 at 20:02
  • Altering the table once a year is IMO the best solution for this (you can create an event for this). There are ways to generate sequential numbers in MySQL but those are much slower than the builtin auto_increment. You can check [this answer](http://stackoverflow.com/questions/11639234/incremental-counter-mysql/11639271#11639271) for generating sequential numbers. – Vatev May 27 '14 at 20:09
  • hmm... for you how do the programmer of my school do my id number like this.. 09001027?... im wondering how :( 09 is 2009 year i enrolled. and its preceed by.. 09001208.. 09001209 – user1046065 May 27 '14 at 20:14
  • It is possible, the simplest way (that I know of) is to use a table with 1 field and 1 row to store the counter, and increment it manually. I edited my previous comment with a link to another answer on that topic. – Vatev May 27 '14 at 20:16
  • ah.. so before insert trigger fires + 1 to that table counter? is that what you mean?.. – user1046065 May 27 '14 at 20:33
  • You could even increment the table counter *inside* your trigger. – Bill Karwin May 27 '14 at 22:00
0

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.

Community
  • 1
  • 1
MER
  • 1,455
  • 20
  • 25