Menu
Is there a way to create a table in MySql that it has an automatic ID field, but the ID is not sequential. For example, a random or pseudo random ID. I have found solutions that suggest generating an ID and try to insert it until an unused ID is found (generating an sequential five digit alphanumerical ID). but nothing that can be done directly in the table definition, or a simpler trick.
382
By default, a primary key is automatically created by taking the object name, adding an ID to the object name, and assigning a primary column value of 1. If you change the value, it must be sequential, unique, and greater than 0. The sequence determines the order in which the primary index is created.
Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.
ALTER TABLE `table` AUTO_INCREMENT = number; Replacing 'number' with the result of the previous command plus one and replacing table with the table name. If you deleted all the rows in the table, then you could run the alter table command and reset it to 0.
In MySQL, the syntax to change the starting value for an AUTO_INCREMENT column using the ALTER TABLE statement is: ALTER TABLE table_name AUTO_INCREMENT = start_value; table_name. The name of the table whose AUTO_INCREMENT value you wish to change.
MySQL has a native function UUID() which will generate a globally unique identifier:
mysql> SELECT UUID();
-> '6ccd780c-baba-1026-9564-0040f4311e29'
You can store its output in a CHAR(36) column.
INSERT INTO table (`uuid`, `col1`, `col2`) VALUES (UUID(), 'someval', 'someval');
According to the documentation though,
Although UUID() values are intended to be unique, they are not necessarily unguessable or unpredictable. If unpredictability is required, UUID values should be generated some other way.
Addendum Another option is UUID_SHORT() for a 64-bit unsigned INT rather than a character field.
mysql> SELECT UUID_SHORT();
-> 92395783831158784
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
