求一条mysql5.0的触发器语句 语句主要实现在“Product”表中插入一条记录时,将主键“Product_id”变成“CP001”、“CP002”.....的形式 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 Product_id原来的形式是什么不一定要用触发器,用函数也可以 你的 insert 语句是什么? mysql> create table t_zqaq_520(product_id char(5) primary key default '',col int);Query OK, 0 rows affected (0.08 sec)mysql>mysql> delimiter |mysql>mysql> CREATE TRIGGER tr_t_zqaq_520_bi BEFORE INSERT ON t_zqaq_520 -> FOR EACH ROW BEGIN -> declare max_id int; -> -> select substring(max(product_id),3) into max_id from t_zqaq_520; -> if max_id is null then -> set max_id=0; -> end if; -> set max_id=max_id+1; -> set new.product_id = concat('CP',right(10000+max_id,3)); -> END; -> |Query OK, 0 rows affected (0.05 sec)mysql>mysql> delimiter ;mysql> insert into t_zqaq_520 (col) values (1);Query OK, 1 row affected (0.08 sec)mysql> select * from t_zqaq_520;+------------+------+| product_id | col |+------------+------+| CP001 | 1 |+------------+------+1 row in set (0.00 sec)mysql> insert into t_zqaq_520 (col) values (2);Query OK, 1 row affected (0.06 sec)mysql> select * from t_zqaq_520;+------------+------+| product_id | col |+------------+------+| CP001 | 1 || CP002 | 2 |+------------+------+2 rows in set (0.00 sec)mysql> 其实你自己看一下MYSQL文档中的例子就应该可以写出来了。MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html 呵呵~成功了~其实我对sQL并不太熟悉呀~谢谢楼上的仁兄~分给你了! trim函数求解 如何解决TEXT类型列不能成为主健或主健组成部分的问题? mysql的log 生僻字显示乱码问题 3台机器mysql数据同步失败 导入txt 用的是sqlyog tools/import sql statements 想找个mysql高手,探讨一些问题(在线等) 求一 mysql 查询语句! 小妹请教,如何使用mysql中的lock table? 动态sql的问题,请高人指点 如果删除数据库指定内容? mysql数据文件空闲空间问题
Query OK, 0 rows affected (0.08 sec)mysql>
mysql> delimiter |
mysql>
mysql> CREATE TRIGGER tr_t_zqaq_520_bi BEFORE INSERT ON t_zqaq_520
-> FOR EACH ROW BEGIN
-> declare max_id int;
->
-> select substring(max(product_id),3) into max_id from t_zqaq_520; -> if max_id is null then
-> set max_id=0;
-> end if;
-> set max_id=max_id+1;
-> set new.product_id = concat('CP',right(10000+max_id,3));
-> END;
-> |
Query OK, 0 rows affected (0.05 sec)mysql>
mysql> delimiter ;
mysql> insert into t_zqaq_520 (col) values (1);
Query OK, 1 row affected (0.08 sec)mysql> select * from t_zqaq_520;
+------------+------+
| product_id | col |
+------------+------+
| CP001 | 1 |
+------------+------+
1 row in set (0.00 sec)mysql> insert into t_zqaq_520 (col) values (2);
Query OK, 1 row affected (0.06 sec)mysql> select * from t_zqaq_520;
+------------+------+
| product_id | col |
+------------+------+
| CP001 | 1 |
| CP002 | 2 |
+------------+------+
2 rows in set (0.00 sec)mysql>
其实我对sQL并不太熟悉呀~
谢谢楼上的仁兄~分给你了!