MySQL – Datatype – Data Definition Language (DDL) – GUIDs

Forward

Unfortunately, MySQL does not currently have a native datatype support for GUIDs.

Some of the other RDBMS, have concise native support for GUIDs.

i.e MSSQL has uniqueIdentifier.

 

Lab

From Googling, here is a workable workaround.

DDL

Create Table – datatypeGUID.person

Outline

  1. GUIDs are 32 characters plus the separating hyphens ( – )

Code



create schema if not exists datatypeGUID;

drop table if exists datatypeGUID.person;

create table if not exists datatypeGUID.person
(

	  personID 			varchar(36) not null
      
    , `name`			varchar(120) not null
    , dateofBirth		datetime null
    , gender            char(1) null

	-- Insert
	, addedBy   		varchar(120) not null
							-- default  SESSION_USER()
                            
    , dateAdded         datetime not null
							default CURRENT_TIMESTAMP
                            
	-- Update
	, modifiedBy   		varchar(120) null
                            
    , dateModified      datetime null
    
    , CONSTRAINT `PK_DatatypeGUID.person`
         PRIMARY KEY  CLUSTERED
	 (
	   personID
         )
        
   , UNIQUE `uniqueName`		
	(
	    `name`
        )
        
)
;

DDL – Trigger

Outline

  1. Unfortunately, MySQL does not allow one to use functions when defining column defaults.
  2. And, so one has to regulate those definitions to Triggers

Code

DDL – Trigger – datatypeGUID.TR_datatypeGUID.person_Insert

DROP TRIGGER IF exists datatypeGUID.`TR_datatypeGUID.person_Insert`;

DELIMITER $$

CREATE TRIGGER datatypeGUID.`TR_datatypeGUID.person_Insert`
BEFORE INSERT ON datatypeGUID.person
FOR EACH ROW
BEGIN

	/*
		If Person ID is null, then set uuid()
	*/		
	if 	( NEW.personID is null ) then
        
		set NEW.personID = uuid();
            
	end if;

	/*
		If Added By is null, then set to current system user
	*/		
	if 	( NEW.addedBy is null ) then
        
		set NEW.addedBy = USER();
            
	end if;     
    
END;    
$$

delimiter ;



DML

DML – Add Data

Code


truncate table datatypeGUID.person;

insert into datatypeGUID.person
(
	`name`, dateofBirth, gender
)
/*
	Jim Jackson (basketball) - Wikipedia, the free encyclopedia
	James Arthur Jackson (born October 14, 1970) is an American retired professional basketball player. 
*/    
select 'Jimmy Jackson', '1970-10-14', 'M'
union
/*
    Kendall Cedric Gill (born May 25, 1968) is an American retired professional basketball player, who now works as a television basketball analyst.
    https://en.wikipedia.org/wiki/Kendall_Gill
*/
select 'Kendall Cedric Gill', '1968-05-25', 'M'


DML – Query Data

Code


select *
from   datatypeGUID.person

Output

getData

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s