Generate Unique Sequential Numbers

       
mysql>
mysql> CREATE TABLE invoice
    -> (id       INT
    -> ,customer VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE invoice3
    -> (id       CHAR(7)
    -> ,customer VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO invoice3(id) VALUES ('INV0001');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO invoice3(id) VALUES ('INV0002');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> INSERT INTO invoice(id, customer)
    ->   SELECT COALESCE(MAX(id),0)+1, 'Shoe World' FROM invoice;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO invoice(id, customer)
    ->   SELECT COALESCE(MAX(id),0)+1, 'Footware Ltd.' FROM invoice;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT * FROM invoice;
+------+---------------+
| id   | customer      |
+------+---------------+
|    1 | Shoe World    |
|    2 | Footware Ltd. |
+------+---------------+
2 rows in set (0.00 sec)

mysql>
mysql>
mysql> SELECT CONCAT('INV',LPAD(SUBSTRING(COALESCE(MAX(id),'INV0000'),4,4)+1,4,'0'))
    ->   FROM invoice3;
+------------------------------------------------------------------------+
| CONCAT('INV',LPAD(SUBSTRING(COALESCE(MAX(id),'INV0000'),4,4)+1,4,'0')) |
+------------------------------------------------------------------------+
| INV0003                                                                |
+------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>
mysql> DROP TABLE invoice;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE invoice3;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>

Related examples in the same category

1.	CONCAT(, [{, }...])
2.	Use the CONCAT() and LEFT() functions to create registration codes for the employees.
3.	Manipulate retrieved data from the Employees table.
4.	Combining a Date and a Time into a Date-and-Time Value
5.	Canonizing Not-Quite-ISO Date Strings
6.	Add the setting NO_ZERO_IN_DATE to the SQL_MODE system variable.
7.	To combine strings rather than pull them apart, use the CONCAT( ) function.
8.	Concatenating Character Strings
9.	Concatenation can be useful for modifying column values "in place."
10.	Non-ISO results from the CONCAT( ) operation can be converted into ISO format three different ways as follows:
11.	Use Concat to create Initial for the name

Generate Unique Sequential Numbers : CONCAT « String « SQL / MySQL

Related examples in the same category