How to Generate Unique Id in MySQL

How to generate unique id in MySQL?

A programmatic way can be to:

• add a UNIQUE INDEX to the field
• generate a random string in PHP
• loop in PHP ( while( ! DO_THE_INSERT ) )
  
  
  • generate another string

Note:

• This can be dirty, but has the advantage to be DBMS-agnostic
• Even if you choose to use a DBMS specific unique ID generator function (UUID, etc)
  it is a best practice to assure the field HAS to be UNIQUE, using the index
• the loop is statistically not executed at all, it is entered only on insert failure

How to generate unique id for pair strings on certain conditions in MySQL

I put this into a SQL Fiddle here: http://sqlfiddle.com/#!2/83bdc/25

Assuming your table name is messages then here is what I would do:

Select messages.*, FirstThread 
From messages

Inner Join

(
-- Find a pair of dudes for each message and show the earliest thread of each
Select ThreadsForPairs.*, FirstThread From

(

Select mid
, Min(recipient) AS FirstDude
, Max(recipient) AS SecondDude
, thread

From messages

Group By mid, thread

) ThreadsForPairs

Left Join

(
-- Find the earliest thread for every pair of dudes
Select FirstDude, SecondDude, Min(thread) AS FirstThread

From

-- For every message, get the dudes involved
(
Select mid
, Min(recipient) AS FirstDude
, Max(recipient) AS SecondDude
, thread

From messages

Group By mid, thread
) PairsForMessages

Group By FirstDude, SecondDude

) FirstThreadForPairs

ON ThreadsForPairs.FirstDude = FirstThreadForPairs.FirstDude
AND ThreadsForPairs.SecondDude = FirstThreadForPairs.SecondDude

) FirstThreadForEveryMessage

On messages.mid = FirstThreadForEveryMessage.mid

As you will see, I do not match your output exactly because message ID 3 would have thread 3 instead of 2, but I think it is the same idea...

I want to automatically generate unique id in sql

From the MySQL documentation, only the NDB storage engine supports computed columns. My guess is that you are using the default storage engine InnoDB, so you would not be able to use computed columns.

In addition, you seem to be mixing SQL Server and MySQL syntax.

Taking both of these into account, I suggest using the following create statement, and then building a view on top of your table which generates the item ID you want:

CREATE TABLE Foods (
    ID int NOT NULL AUTO_INCREMENT,
    item_name varchar(10),
    price int,
    PRIMARY KEY(ID)
);

Then, create a view which can turn out the computed items you want:

CREATE VIEW Foods_View AS (
    SELECT
        ID,
        RIGHT(CONCAT('00000000', CAST(ID AS CHAR(8))), 8) AS ItemId,
        item_name,
        price
    FROM Foods
)

Generate Unique Long ID for a view in MySql

UPDATE: When you insist to have it as a number, create your view like this:

SELECT
(@rownum:=@rownum + 1) AS rownumber,
yourTable.*
FROM
yourTable
, (SELECT @rownum:=0) r

But that's really it - no more other possibilities. Cast rownumber as long like I said in comments, if it really, really has to be long.

Alternatively in a procedure:

DELIMITER $$
CREATE PROCEDURE selectFromWhatever()
BEGIN
SELECT
(@rownum:=@rownum + 1) AS rownumber,
yourTable.*
FROM
yourTable
, (SELECT @rownum:=0) r;
END $$
DELIMITER ;

Then get result with

CALL selectFromWhatever()

Original answer:

From the MySQL manual:

UUID()

Returns a Universal Unique Identifier (UUID) generated according to
“DCE 1.1: Remote Procedure Call” (Appendix A) CAE (Common Applications
Environment) Specifications published by The Open Group in October
1997 (Document Number C706,
http://www.opengroup.org/public/pubs/catalog/c706.htm).

A UUID is designed as a number that is globally unique in space and
time. Two calls to UUID() are expected to generate two different
values, even if these calls are performed on two separate computers
that are not connected to each other.

A UUID is a 128-bit number represented by a utf8 string of five
hexadecimal numbers in How to Generate Unique Id in MySQL-bbbb-cccc-dddd-How to generate unique id in MySQL? How to generate unique id for pair strings on certain conditions in MySQL I want to automatically generate unique id in sql Generateeeee format:

The first three numbers are generated from a timestamp.

The fourth number preserves temporal uniqueness in case the timestamp value loses monotonicity (for example, due to daylight
saving time).

The fifth number is an IEEE 802 node number that provides spatial uniqueness. A random number is substituted if the latter is not
available (for example, because the host computer has no Ethernet
card, or we do not know how to find the hardware address of an
interface on your operating system). In this case, spatial uniqueness
cannot be guaranteed. Nevertheless, a collision should have very low
probability.

Currently, the MAC address of an interface is taken into account only on FreeBSD and Linux. On other operating systems, MySQL uses a
randomly generated 48-bit number.

mysql> SELECT UUID();
-> '6ccd780c-baba-1026-9564-0040f4311e29'

Warning

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. Note

UUID() does not work with statement-based replication.

Another way would be to use CONCAT() to build your unique ID.

SELECT CONCAT(PRINCIPAL_ID, '-', GROUP_ID) AS myUniqueID
FROM yourTable

---

Related Topics