MySlq creates a fixed-length string primary key and uses a trigger

MySlq creates a fixed-length string primary key. The trigger is used to make the directory's primary key neat and easy to use, so the fixed-length string is used as the primary key. When the table is empty and there is only one data, LAST_INSERT_ID () the value obtained by the method is 1, so the trigger judges drop table if exists tb_cate; create table tb_cate (id char (5) primary key DEFAULT ''comment' directory number, 5-digit number, with a value of 0 in the upper position, starting from 00001 and increasing by 1 each time, the sequence and trigger control ', www.2cto.com type char (1) default 0 COMMENT' directory type, standby, you can set the shared directory to ', name varchar (40) not null COMMENT' directory name', 'describe' varchar (200) comment' directory description', code varchar (30) not null COMMENT 'Directory full encoding, for example, 0000100002, does not contain oneself ',
Parent char (5) COMMENT 'parent directory', levchar (1) default 0 comment' directory level, default 0 level ', create_user int (10) default 0 comment' directory creator, the default value 0 is the system creator, and the last modifier of the update_user int (10) default 0 COMMENT directory. The default value 0 is the system creator, and the createtime datetime COMMENT is the Directory Creation Time ', updatetiem datetime COMMENT 'Directory last modification time', increc_id int unique auto_increment comment' is only used to implement the fields used by the trigger, and this field does not need to be mapped to the object '); delimiter // # required create trigger tb_cate_createid before insert ON tb_cateFOR each row BEGINselect count (increc_id) from tb_cate into @ inid; # determine whether the table is empty if @ inid = 0 then www.2cto.com set new. id = SUBSTRING (concat ('20140901', cast (LAST_INSERT_ID () as char), (char_length (concat ('20140901', cast (LAST_INSERT_ID () as char ))) -4); elseset new. id = SUBSTRING (concat ('20140901', cast (LAST_INSERT_ID () + 1 as char), (char_length (concat ('20140901', cast (LAST_INSERT_ID () + 1 as char)-4); end if; END; // delimiter; # required # The following is the second method, the first method has a problem in the Mysql Workbench test, and the second method has no problem, you have not tested delimiter // # required create trigger tb_cate_createId before insert ON tb_cateFOR each row begin select count (increc_id) from tb_cate into @ inid; if @ inid = 0 then www.2cto.com set new. id = SUBSTRING (concat ('20140901', cast (1 as char), (char_length (concat ('20160901', cast (1 as char)-19 )); elseselect max (increc_id) from tb_cate into @ maxid; set new. id = SUBSTRING (concat ('20140901', cast (@ maxid + 1 as char), (char_length (concat ('20160901 ', cast (@ maxid + 1 as char)-19); end if; END; // delimiter; # required