mysql_insert_id or something like that to return last mysql UUID()

How 开发者_开发问答do you return the last generated UUID() (primary key) - is there something like mysql_insert_id for that?

Table uuidtable:

primary key: uuid uuid()
id_u (index): integer

multiple id_u matched with a primary key uuid()

insert: insert into uuidtable (uuid,id_u) values (uuid(),id)

where id is a number, of course, and uuid is escaped with

 uuid 

Write yourself a trigger like so:

CREATE TRIGGER ai_uuidtable
AFTER INSERT ON uuidtable
FOR EACH ROW
SET @last_uuid = NEW.uuid;

Following an insert:

SELECT @last_uuid

MySQL's user-defined variables are connection-specific, so you don't have to worry about getting another connection's @last_uuid.

One point of concern: If you're using the uuid as a key, in order for it to be maximally performant, it should be stored as a 16-byte binary field and not a 36-byte char field. If you really want to use MySQL's UUID() algorithm, strip out the hyphens and UNHEX() it:

UNHEX( REPLACE( UUID(), '-', '' ) )

Aside: PostgreSQL actually has a UUID data type (but no built-in UUID() function), which just means you don't have to re-HEX() the field in order to avoid getting binary garbage in your terminal on SELECT.

I'm not sure it's even possible to do in mysql.

The only obvious solution I can get of is to generate UUID() in separate query and then insert the record with known id.

To get/return a database-generated-uuid we can use a database-variable:

SET @uuid=UUID();
INSERT INTO uuidtable (uuid,id_u) values (@uuid,$number);
SET @uuid = IF(ROW_COUNT(),@uuid,null);#way1
SELECT @uuid;#way1
#SELECT IF(ROW_COUNT(),@uuid,null) as uuid;#way2

We create the uuid before the insert-query and saves it to '@uuid'. This allows us to use it (for other tables, as foreign key) or return its value, e.g. to use it in your (php-)code, to redirect, call or whatever.

way1: After the insert-query set the variable again, depending of the success of the insert. If the insert failed, our query return is null, so we can handle in our application, and we avoid to use uuid which we thought it was used, but never was.

way2: Just save rows/queries and let the variable existing. Our return is clean, but the variable is still in the memory of the database.

Benefit of this method: We can fire it to the database once and get one return, if we first run a select to get an uuid and insert after, we could get a potential double-delay by the db-driver-network-connection.

Following on from Richard's solution, you can actually use a trigger to get all the functionality you'd expect from a regular autoincrementing ID:

CREATE TRIGGER `tablename_newid` 
BEFORE INSERT ON `tablename` 
FOR EACH ROW 
BEGIN 
    SET NEW.table_id = UNHEX(REPLACE(UUID(),'-',''));
    SET @last_uuid = NEW.table_id; 
END

This will allow you to perform inserts without manually inserting the UUID primary key, and automatically store the auto-generated ID into the @last_uuid variable. To read it back out, just SELECT @last_uuid as before.

Richard's answer uses AFTER INSERT, which avoids the issue with BEFORE INSERT where the UUID variable is set even when a row insert fails. I haven't tested this method - while I feel that it would be totally fine under normal circumstances I wonder whether there would be issues with cluster replication.

Notes on performance: You'll notice my UUID insertion runs through REPLACE() and UNHEX() to store it as a 16-byte binary field - this is only 4x the storage space of a normal INT primary key (and only twice that of a BIGINT), and will be much faster to query than string-based UUID keys.

When querying and reading such binary values, the MySQL functions HEX() and UNHEX() will be very helpful, as will writing your query values in hex notation (preceded by 0x).

Hi the only sure way I found is to store uuid in a variable. Then use the variable in the insert query as follows:

DELIMITER \\
CREATE PROCEDURE AddUserLinkRole(uname VARCHAR(50), passwd VARCHAR (40), role VARCHAR(50))
    BEGIN
        DECLARE UserUuid VARCHAR(36);
        DECLARE UserRole VARCHAR(50);
        /*Now initiate UserUuid = uuid() value*/
        SET UserUuid = uuid();
        SELECT RoleID INTO UserRoleID FROM Roles WHERE Description = role;
        /*Because I initiated UserUuid = uuid(), I can now refer to it anytime I wish using my UserUuid variable*/
        INSERT INTO Users(UserID,UserName,Password,RoleID) VALUES (UserUuid,uname,MD5(passwd),UserRoleID);
    END \\
DELIMITER ;

I hope the above makes sense..

For a specific table I'd like to move from an unsigned integer, auto increment id to uuid_short() id.

The table looks like this

CREATE TABLE IF NOT EXISTS `person` (
  `person_id` bigint(20) NOT NULL DEFAULT uuid_short(),
  `name` varchar(128) NOT NULL,
  `vorname` varchar(128) NOT NULL,
  `gender` char(1) NOT NULL DEFAULT 'u',
  PRIMARY KEY (`person_id`),
  KEY `gender` (`gender`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The insert query is generated with a prepared statement setting and binding all the values.

If I do this the PDO::lastInsertID is always 0 (zero) where in face the update is done correctly with a proper key in the table

However, if I use an unsigned int:

 `person_id` int(20) UNSIGNED NOT NULL AUTO_INCREMENT,

all is well and the newly created id is stored in lastInsertId

Is there an alternative to find the last created PK if the default is uuid_short() ?

If you're using MariaDB, you don't have to create a trigger for each table.
Instead, you can easily retrieve the UUID by adding a RETURNING clause to your query:

INSERT INTO uuidtable (uuid, id_u) VALUES (UUID(), id) RETURNING uuid;