Hi Jason,
For MyISAM tables only, you can manually set the auto
increment counter using the syntax:
ALTER TABLE table_name AUTO_INCREMENT = 1000
Keep in mind, however, that this does not change the
value of the LAST_INSERT_ID() since it still
represents the value last inserted. On a freshly
created table, that is 0. So if you create a table,
alter the AUTO_INCREMENT value to 1000, until you
insert a record, LAST_INSERT_ID() will return 0. Once
you insert a record, LAST_INSERT_ID() will then return
1000 (or whatever value you used in the ALTER
statement). (See below for an example session).
Another major caveat (also illustrated below) is that
the value returned by LAST_INSERT_ID() is
session/connection specific and therefore cannot be
trusted in the manner you are thinking of, assuming
you plan to create your ticket number using a syntax
similar to:
INSERT INTO tickets SET ticket_num =
CONCAT(FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y%m'),
LAST_INSERT_ID());
Lastly, you will not be able to use the ALTER TABLE
table_name AUTO_INCREMENT = 1000 command to reset the
ticket number to a starting value each month (if that
was your intent).
There are a lot of little gotchas when using
AUTO_INCFREMENT and especially LAST_INSERT_ID() for
anything other than a simple incremental counter. You
may have to get rather sophisticated in the way you
solve your problem; someone else on the form may have
some ideas from experience. Nevertheless, whatever
your solution ends up being, I highly recommend you
test the heck out it, and make sure you test with
concurrent connections/sessions.
Hope that helps :)
Good Luck,
Mark
===START EXAMPLE===
~From Session 1~
mysql> CREATE DATABASE testdb;
Query OK, 1 row affected (0.00 sec)
mysql> use testdb;
Database changed
mysql> CREATE TABLE table01(id SMALLINT(4) UNSIGNED
ZEROFILL NOT NULL AUTO_INCREMENT, PRIMARY KEY(id),
data VARCHAR(10));
Query OK, 0 rows affected (0.03 sec)
mysql> SELECT LAST_INSERT_ID();
+------------------+
| last_insert_id() |
+------------------+
| 0 |
+------------------+
1 row in set (0.00 sec)
mysql> ALTER TABLE table01 AUTO_INCREMENT = 1000;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT LAST_INSERT_ID();
+------------------+
| last_insert_id() |
+------------------+
| 0 |
+------------------+
1 row in set (0.00 sec)
mysql> INSERT INTO table01 SET data=CONCAT('last=',
LAST_INSERT_ID());
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO table01 SET data=CONCAT('last=',
LAST_INSERT_ID());
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM table01;
+------+-----------+
| id | data |
+------+-----------+
| 1000 | last=0 |
| 1001 | last=1000 |
+------+-----------+
2 rows in set (0.00 sec)
~From Session 2~
mysql> use testdb;
Database changed
mysql> INSERT INTO table01 SET data=CONCAT('last=',
LAST_INSERT_ID());
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO table01 SET data=CONCAT('last=',
LAST_INSERT_ID());
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM table01;
+------+-----------+
| id | data |
+------+-----------+
| 1000 | last=0 |
| 1001 | last=1000 |
| 1002 | last=0 |
| 1003 | last=1002 |
+------+-----------+
4 rows in set (0.00 sec)
~Back to Session 1~
mysql> INSERT INTO table01 SET data=CONCAT('last=',
LAST_INSERT_ID());
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO table01 SET data=CONCAT('last=',
LAST_INSERT_ID());
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO table01 SET data=CONCAT('last=',
LAST_INSERT_ID());
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM table01;
+------+-----------+
| id | data |
+------+-----------+
| 1000 | last=0 |
| 1001 | last=1000 |
| 1002 | last=0 |
| 1003 | last=1002 |
| 1004 | last=1001 |
| 1005 | last=1004 |
| 1006 | last=1005 |
+------+-----------+
7 rows in set (0.00 sec)
Notice the "flux" in the last value due to the reasons
described above.
===END EXAMPLE===
--- Jason Williard <[EMAIL PROTECTED]> wrote:
> I have a ticketing system that sets the ID of the
> ticket as the
> LAST_INSERT_ID. By default, it was counting up from
> 0. I updated the
> ID of the last ticket to reflect a different
> numbering scheme
> (YYYYMMxxxx). I would like to automate this but I
> don't want to change
> the ID of a current ticket. I was hoping that there
> was a way to update
> the LAST_INSERT_ID. Is this possible? If so, what
> is the query that
> should be run?
>
> Thank You,
> Jason Williard
> Janix
>
__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
