Set a datetime column with a SQL trigger

Question

I using a MySQL server (5.5.27 - Community Server). I have a table with this definition:

CREATE  TABLE IF NOT EXISTS tbl_messages (
  `msg_id` VARCHAR(50) NOT NULL ,
  `msg_text` VARCHAR(50) NULL ,
  PRIMARY KEY (`msg_id`);

I write a trigger that, when I do an insert, the server sets the msg_id column with the current time including microseconds with this format "yyyymmddhhnnssuuuuuu". "u" is for microseconds.

I created a trigger:

create trigger tbl_messages_trigger
before insert on tbl_messages 
for each row

BEGIN
    SET NEW.msg_id = DATE_FORMAT(NOW(),'%Y%m%d%H%i%s%f'); 
END;$$

But the msg_id column only gets values like this: 20130302144818*000000*, with microseconds in zero. ¿Is it possible capture the microseconds?

TIA,

It seems that with MySQL version 5.5 it's impossible, and I need 5.6.4 version... http://stackoverflow.com/questions/8979558/mysql-now-function-with-high-precision http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-4.html — Mario S, Mar 02 '13 at 20:09

Pavel Malinnikov · Accepted Answer · 2013-03-03T04:54:13.760

From the code provided I guess that you are trying to use microsecond to minimize probability of getting same msg_id for different rows.

Also, msg_id is the primary key, which should not present any object-specific data but only be unique. There is a good link about: http://en.wikipedia.org/wiki/Surrogate_key

The best way to deal with primary keys in MySql is AUTO_INCREMENT column attribute. If you need insert time for messages, you may provide column for it:

CREATE TABLE tbl_messages 
(
   `msg_id` bigint(20) NOT NULL AUTO_INCREMENT,
   `msg_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   `msg_text` VARCHAR(50) NULL,
    PRIMARY KEY (`msg_id`)
);

That's what I looking for! With bigint, I need 58 billions of years, at 1000 new records per second to finishing it. Thanks! — Mario S, Mar 08 '13 at 02:36

Set a datetime column with a SQL trigger

1 Answers1