![\"\"](\"http:\/\/en.latindevelopers.com\/ivancp\/wp-content\/uploads\/2012\/02\/logo-mysql-170x115.png\" "\\"MySQL"){kind=logo}
Those who have used PostgresSQL usually gets lazy when write the SQL code to auto-numeric fields, but we miss that feature when we are in a MySQL database.<\/p>\nThose who have used PostgresSQL usually gets lazy when write the SQL code to auto-numeric fields, but we miss that feature when we are in a MySQL database.<\/p>\n
As we know, MySQL have auto-increment fields, that are very useful too, but we miss that feature when we are in a PostgreSQL database.<\/p>\n
I will try to explain how to simulate sequences like PostgreSQL in MySQL. But in what situations we would need sequences in MySQL? some ideas:<\/p>\n
OK let’s start:<\/strong><\/p>\n We need to create a table to store the current sequence value (if this table is created inside\u00a0mysql database\u00a0 is not bad idea)<\/p>\n Now we need a function to get the next sequence value, including these cases:<\/p>\n There is the function\u00a0 <\/p>\n The next step is to create\u00a0 (to maintain purposes) a stored procedure to modify the current sequence value:<\/p>\n <\/p>\n That’s it!<\/strong>, just one more thing: testing. Creating a table with two auto-numeric fields using simulated sequences:<\/p>\n We must create a before-insert trigger on table:<\/p>\n <\/p>\n Run some tests:<\/p>\n <\/p>\n What do you think?<\/p>\n This simulated sequence don’t consider some stuffs but it works and can be helpful. Even you can modify the function to make more complex generation of next value.<\/p>\ncreate table _sequence\r\n(\r\n\tseq_name varchar(50) not null primary key,\r\n\tseq_val int unsigned not null\r\n);<\/pre>\n
\n
\n
getNextSeq<\/code> it receive sequence name as parameter:<\/p>\ndelimiter \/\/\r\ndrop function if exists getNextSeq\/\/\r\n\r\ncreate function getNextSeq(sSeqName varchar(50)) returns int unsigned\r\nbegin\r\n declare nLast_val int;\r\n\r\n set nLast_val = (select seq_val\r\n from _sequence\r\n where seq_name = sSeqName);\r\n if nLast_val is null then\r\n set nLast_val = 1;\r\n insert into _sequence (seq_name,seq_val)\r\n values (sSeqName,nLast_Val);\r\n else\r\n set nLast_val = nLast_val + 1;\r\n update _sequence set seq_val = nLast_val\r\n where seq_name = sSeqName;\r\n end if;\r\n\r\n return nLast_val;\r\nend\/\/\r\n\r\ndelimiter ;<\/pre>\n
drop procedure if exists sp_setSeqVal\/\/\r\n\r\ncreate procedure sp_setSeqVal(sSeqName varchar(50), nVal int unsigned)\r\nbegin\r\n if (select count(*) from _sequence where seq_name = sSeqName) = 0 then\r\n insert into _sequence (seq_name,seq_val)\r\n values (sSeqName,nVal);\r\n else\r\n update _sequence set seq_val = nVal\r\n where seq_name = sSeqName;\r\n end if;\r\nend\/\/\r\n\r\ndelimiter ;<\/pre>\n
create table multiple_autonums\r\n(\r\n id int not null primary key auto_increment,\r\n seq_1 int, -- sequence 1\r\n seq_2 int, -- sequence 2\r\n unique(seq_1),\r\n unique(seq_2)\r\n);<\/pre>\n
delimiter \/\/\r\ndrop trigger if exists multiple_autonums_ai\/\/\r\n\r\ncreate trigger multiple_autonums_bi before insert on multiple_autonums\r\nfor each row\r\nbegin\r\n set new.seq_1 = getNextSeq(\"seq_1\");\r\n set new.seq_2 = getNextSeq(\"seq_2\");\r\nend\/\/\r\n\r\ndelimiter ;<\/pre>\n
-- inserting new values.\r\ninsert into multiple_autonums (id) values (null),(null),(null);\r\n\r\n-- getting the inserted values\r\nselect * from multiple_autonums;\r\n+----+-------+-------+\r\n| id | seq_1 | seq_2 |\r\n+----+-------+-------+\r\n| 1 | 1 | 1 |\r\n| 2 | 2 | 2 |\r\n| 3 | 3 | 3 |\r\n+----+-------+-------+\r\n3 rows in set (0.00 sec)\r\n\r\n-- Changing sequence current values\r\n\r\ncall sp_setSeqVal(\"seq_1\",47);\r\ncall sp_setSeqVal(\"seq_2\",9786);\r\n\r\n-- Inserting new values\r\ninsert into multiple_autonums (id) values (null),(null),(null);\r\n\r\n-- Seeing what happen.\r\nselect * from multiple_autonums;\r\n+----+-------+-------+\r\n| id | seq_1 | seq_2 |\r\n+----+-------+-------+\r\n| 1 | 1 | 1 |\r\n| 2 | 2 | 2 |\r\n| 3 | 3 | 3 |\r\n| 4 | 48 | 9787 |\r\n| 5 | 49 | 9788 |\r\n| 6 | 50 | 9789 |\r\n+----+-------+-------+<\/pre>\n
\nHope you find it useful.<\/div>\n<\/div>\nShare this:<\/h3>