Add UUID field to MySQL

-- ----------------------------------------------------------
-- ADDING TO DATABASE
-- ----------------------------------------------------------

-- add UUID column
ALTER TABLE test.tblapps DROP COLUMN uuid;
ALTER TABLE test.tblapps ADD COLUMN uuid BINARY(16) AFTER fldAppID;

-- update new field with UUIDs generated by MySQL
UPDATE test.tblapps set uuid = UNHEX(REPLACE(UUID(),'-',''));

-- insert record with known UUID
INSERT INTO tblApps (uuid, fldName) VALUES (UNHEX('2F7D9DAC192C4867B83CEC97447C3FDA'),'Test 1');

-- select all rows (note UUID field is unreadable)
SELECT * FROM tblapps;

-- select fields and HEX representation of UUID (UUID is readable)
SELECT fldAppID, hex(uuid), fldName FROM tblapps;

-- compare UUID for known hex value. Note the "x" before the value!
select fldAppID, fldName FROM tblapps where uuid = x'2F7D9DAC192C4867B83CEC97447C3FDA';

-- ----------------------------------------------------------
-- MISCELLANEOUS
-- ----------------------------------------------------------

-- examples of UUID funtion
select uuid();
select UNHEX(REPLACE(UUID(),'-',''));

-- create readable HEX value (you would never do this IRL)
select hex( UNHEX(REPLACE(UUID(),'-','')) );

-- this "works" but, you cannot read it
select UNHEX('2F7D9DAC192C4867B83CEC97447C3FDA');
-- to make readable "hex it" (again never do this)
select HEX(UNHEX('2F7D9DAC192C4867B83CEC97447C3FDA'));


-- This will not work - too big with dashes
UPDATE test.tblapps set uuid=UUID();


-- using code (PHP) re-insert the dashes at the positions 9, 14, 19 and 24 to match your original UUID
-- or leave as is (we really don't care about the dashes or what they represent - we just want unique
-- values).