Back in 2000 I posted a tip on using SQL Server triggers to obtain assigned identity field values. Someone recently asked if this was doable in MySQL, and the answer is yes, now that MySQL 5 finally provides basic trigger support. Suppose you had a table named products which contained a column named product_id which was defined as AUTO_INCREMENT. You could use the following statement to create a trigger which would return the newly assigned product_id each time a new product was inserted.
CREATE TRIGGER newproductid AFTER INSERT ON products
FOR EACH ROW SELECT NEW.product_id;
Obviously, this would require MySQL 5 to work.
Determining MySQL AUTO_INCREMENT Values
7 responses to “Determining MySQL AUTO_INCREMENT Values”
-
Just a little tid bit to add:
I have always been taught to use uniqueidentifiers (UUID) in SQL Server for primary keys. The main reason being is that it sets the way if the database ever need to become part of a replication group or take part in a cluster. I personally have found them alot easier to use then using the int data type and an identity insert.
The best part about it is that you don’t need to write triggers and what not to retrieve the last inserted value from the database. All you really have to do is have MSSQL create a UUID for you before you do the insert and then use that value when doing your insert as the primary key. An example would be
<cfquery name="test" datasource="mydsn">
SELECT newid() AS myuuid
</cfquery>
<cfquery name="testinsert" datasource="mydsn">
insert into table
(pk, firstname, lastname)
VALUES
(#test.myuuid#, ‘tony’, ‘petruzzi’)
</cfquery>
An important thing to remember for anyone using MSSQL. Remember that MSSQL DOES NOT support row level triggers. So you will have to modify the example that Ben give in his post to compensate for this feature lack:
CREATE TRIGGER newproductid FOR products
AFTER INSERT
AS
SELECT product_id FROM inserted
I’ll shut up now. -
Wouldn’t it be better to return the whole record?
-
What about speed issues. UUID’s must be inherently slower than incremental IDs.
I would also say that from personal experience, should you need to debug/examine records, the human mind has less of an issue remembering an ID compared with a 35 char UUID.
Another issue is your use of a query to generate a UUID. There is a function within CF to create UUIDs. You’ve slowed your template/cfc down by hitting the DB just to get a UUID. Just select the index cursor (as part of the insert).
I would state now that your system architecture will determine whether you perceive the use of UUIDs to be necessary.
However declaring UUIDs to be the generally correct way forward is a bit OTT. In n-tier applications with bi-directional (right word?) replication this may be acceptable.
As to using MSSQL…personally I like Postgre SQL. MySQL5 will bring in lot of new features that have been mature in Postgre for a long time.
Adam -
There is no degrade in performace with hitting the database to grab a UUID. You are doing about the same thing when using another query or trigger to grab the incremental value when using an identity column. I’ve tried out and throughly tested both methods and couldn’t get a difinitive speed difference between them. I find this way easier, so that’s why I use it.
Yes I am aware that CF has a UUID function, CreateUUID. Were you aware of the problems that this function has associated with it in creating duplicate UUIDs and the plain fact that the UUIDs created using this function are not compatable with MSSQL. Yes there is a hack to get them to work by inserting an extra hyphen, but that’s hack and not supported. Don’t take my word for it, read the livedocs and the CF7 documentation and you will see the problems I stated.
I’ve always said that the way I do things are my own. If you do things differently, that’s fine and I respect that.
I don’t mind people telling I’m wrong or calling an idiot for something I do or say. I just don’t like people doing it without researching what I bring to the table first. -
I am not a big fan of having client code determine unique id values, be it CF or any other client. Doing so means that you cannot easily use other clients to insert data. If you want to use a UUID, do so, but let the database generate it (either using another query, or a INSERT BEFORE trigger, or something else). Neither CF, not any other client software, should be generating database unique IDs.
-
First off, you shouldn’t use triggers to select any type of data, especially with MySQL. If you have an auto_increment which must be a primary key, you use this code after the INSERT:
<cfquery name=’query1′ datasource=’datasource’>
INSERT INTO
table
SET
columnname = ‘value’;
</cfquery>
<cfquery name=’query2′ datasource=’datasource’>
SELECT
last_insert_id() lastid
</cfquery>
<cfoutput>
#variables.query2.lastid#
</cfoutput>
I haven’t read much about UUIDs part that you guys/girls wrote about, but don’t use them. First off, for auto_increment or unique values, never use UUIDs, that’s like 8 bytes vs 2 bytes. In MySQL, mediumint usually satisfies most applications.
In Coldfusion, don’t use createuuid(), you are better off using randrange() and creating your own combination of values. Put createuuid() in a small loop, and look at the time it takes to execute the page, you won’t think twice after that. -
Do not use uniqueidentifiers (UUID) in for primary keys. First of all, the storage aspect is an issue since there are more bytes, there will be more index pages to maintain. Secondly, and most importantly the primary key is generally clustered. This means that the values are maintained in order. An auto incrementing type of identity is most appropriate here since it is ever increasing resulting in no page swapping. Since UUID order is all over the place, you would seriously degrade database performance over time as stuff would be swapping out all over the place.
Say you have a page that contains these values A,C,D,E. Now this page can only contain 4 values. If I now insert a "B", then the "E" would have to be swapped out and placed on another page, or even worse a new page would have to be created. The clustered index might decide to leave the slot empty resulting in a highly fragmented index.
Leave a Reply