Use of MySQL triggers

Original requirements:

Number field: L + Month day + 10 digit serial number, for example: L201502090000000001 the second day of data 10-bit serial number is starting from 1

The test data tables and triggers are built as follows:

set foreign_key_checks=0;-- ------------------------------ Table structure for  gztest-- ----------------------------drop table if exists  ' gztest '; create table  ' Gztest '   (   ' id '  int (one)  not null auto_increment,    ' code '  bigint  DEFAULT NULL,   ' content '  char (255)  default  NULL,  PRIMARY KEY  (' id '))  engine=innodb auto_increment=0 default  CHARSET=utf8;DROP TRIGGER IF EXISTS  ' GetCode ';D elimiter ;; create trigger  ' GetCode '  BEFORE INSERT ON  ' gztest '  for each row  BEGINdeclare newcode BIGINT; Set newcode= (select  ' code '  FROM gztest WHERE  ' Code '  like concat (date_ FORMAT (Curdate (), '%y%m%d '), '% ')  ORDER BY  ' code '  desc limit 1); if newcode  THEN    SET NEW.code=newcode+1; Else  set new.code=concat (Date_format (Curdate (), '%y%m%d '), ' 0000000001 '); end if; END;;D elimiter ;

This article is from the "connected with the Internet" blog, please be sure to keep this source http://suiwnet.blog.51cto.com/2492370/1613398

Use of MySQL triggers