mysql 查询当前时间到毫秒_如何从MySQL当前时间获取微秒或毫秒？

I am trying to create my first stored function on MySQL. In this function I want to return the timestamp of the current date and time with 3 microsecond digits like this: YYYYMMDDHHMMSSZZZ

I use this number in my database to create unique keys for my records offline so they don't crash when my systems merge databases from different offline servers.

So my first try for that was SELECT CAST(MICROSECOND(NOW()) AS CHAR(3));

But it returns 0.

If I try SELECT CAST(MICROSECOND('2009-12-31 23:59:59.001210') AS CHAR(3));

It returns 121, as I need.

So, How to tell MySQL that I want to know the microseconds of the current time?

EDIT:

Consider this:

CREATE FUNCTION CHAVE (pTable VARCHAR(32)) RETURNS CHAR(20)

BEGIN

DECLARE vSigla CHAR(3);

DECLARE vDateTime CHAR(14);

DECLARE vMilli CHAR(3);

DECLARE vKey CHAR(20);

SET vSigla = (SELECT SIGLA FROM TABLELIST WHERE NOME = pTable);

SET vDateTime = (SELECT CAST(LEFT(UTC_TIMESTAMP()+0, 14) AS CHAR(14)));

SET vMilli = LPAD(FLOOR(RAND() * 1000), 3, '0');

SET vKey = CONCAT(vSigla, vDateTime, vMilli);

RETURN vKey;

END;

The result of:

INSERT INTO TABLEX (dateID, name) VALUES (CHAVE('TABLEX'), 'EASI');

Will be, from CHAVE('TABLEX'):

KEY20130320151159666

Where 666 will be a random number, but I wish it was the real milliseconds count of the current time, so I have no possible duplicated key.

If only I could use SHOW COLUMNS FROM @TableName WHERE FIELD_NAME LIKE '%_ID' LIMIT 1 and insert that in a non-dynamic SELECT to get the millisecond of the last record of that table...

解决方案

MySQL 5.6 supports the millisecond precision in the sysdate function.

try

select sysdate(6) will return 2013-04-16 13:47:56.273434

and

select sysdate(3) will return 2013-04-16 13:47:56.273