Tag Archives: uuid

PostgreSQL 8.3 adding uuid-ossp on Debian

Posted on by

UUID is a nice way to generate (almost) unique id’s.

However on PostgreSQL 8.3 the data type exists, but if you wish to be able to generate UUID’s you need to get a module (in this case uuid-ossp).

On Debian this module is in the contrib package, so if you have not installed this before do it now

user@server:~$ sudo apt-get install postgresql-contrib

Then you need to “load” the module into PostgreSQL; or more accurately you tell PostgreSQL about the new functions this module has, how to reach them etc.
Depending on you security settings (in pg_hba.conf) you may need to log in as the postgres user on your system.

postgres@sever:~$ psql -d MYDB -U postgres -f /usr/share/postgresql/8.3/contrib/uuid-ossp.sql

MYSQL Creating several UUIDs at one single time

Posted on by

I recently ran into the problem that I had too add two rows to a database that uses a UUID (in this case converted to a binary) as a key.
The problem I had was that running two insert statements after each other (just separated by “;”) then the same id was generated.
A workaround I have found is to sleep for a second between the two statements.

Examples (using SELECT instead of INSERT):
Getting the same id twice

SELECT UNHEX(REPLACE(UUID(),'-','')) AS uuid1,  UNHEX(REPLACE(UUID(),'-','')) AS uuid2;
-- Shows: "849392548f1311df91b70019dbd264f1"	"849392908f1311df91b70019dbd264f1"

Adding a sleep to get different ids

SELECT UNHEX(REPLACE(UUID(),'-','')) AS uuid1, SLEEP(1), UNHEX(REPLACE(UUID(),'-','')) AS uuid2
-- Shows: "3901c1928f1511df91b70019dbd264f1"	"0"	"399a5b148f1511df91b70019dbd264f1"

And another one using INSERTS

INSERT INTO testTable (id) VALUES (UNHEX(REPLACE(UUID(),'-','')));
SLEEP(1);
INSERT INTO testTable (id) VALUES (UNHEX(REPLACE(UUID(),'-','')));

PHP: converting a uuid stored as BINARY(16) in mysql to RFC 4122 format

Posted on by

If you are storing uuid’s are BINARY(16) in the database, then the following will turn the stored id to the “original” format.

$uuidReadable = unpack("h*",$uuid);
$uuidReadable = preg_replace("/([0-9a-f]{8})([0-9a-f]{4})([0-9a-f]{4})([0-9a-f]{4})([0-9a-f]{12})/", "$1-$2-$3-$4-$5", $uuidReadable);
$uuidReadable = array_merge($uuidReadable);
echo "uuid is " . $uuidReadable[0];

MySQL inserting UUID to a binary(16) field

Posted on by

It is advantageous to store a UUID (acc to RFC 4122) in a binary(16) field in the database.
It is not hard to create such a table and to insert data into it.

CREATE TABLE IF NOT EXISTS test_table (
  id BINARY(16) NOT NULL,
  name VARCHAR(128) NOT NULL,
  PRIMARY KEY  (id)
) 
 
INSERT INTO test_table (id, name) VALUES
(UNHEX(REPLACE(UUID(),'-','')), 'test1'),
(UNHEX(REPLACE(UUID(),'-','')), 'test2'),
(UNHEX(REPLACE(UUID(),'-','')), 'test3')