如何使表中的某个域的值唯一? 数据表中某个域,里面的值只能是“true”或者是“false”,同时只能让一条记录是“true”,其他都是“false”在MySql中是如何做到的,谢谢! 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 mysql目前还不支持 check约束,所以只能通过 触发器来实现 +------------+------+-------+----------+----------+| discountid | rate | count | discount | selected |+------------+------+-------+----------+----------+| 1 | 0.8 | 0 | 0 | TRUE || 2 | 0.2 | 20 | 125.0 | TRUE || 3 | 0.9 | 50 | 300 | FALSE |+------------+------+-------+----------+----------+在selected这个域中,只能有一个TRUE,可以有很多的FALSE。理解了么? 在selected这个域中,只能有一个TRUE,可以有很多的FALSE。:·什么标准? 整个表中只允许有一个 true ? 你给的例子已经有两个 true 了。 discountid是auto_increment的primary key 见如下例子。mysql> create table ta ( -> discountid int auto_increment primary key , -> `selected` BOOL -> );Query OK, 0 rows affected (0.09 sec)mysql>mysql> delimiter |mysql>mysql> CREATE TRIGGER ta_bi BEFORE INSERT ON ta -> FOR EACH ROW BEGIN -> DECLARE bExist int default 0; -> select count(*) into bExist from ta where selected; -> if bExist>0 and new.selected then -> set new.discountid=1; -> end if; -> END; -> |Query OK, 0 rows affected (0.08 sec)mysql>mysql> delimiter ;mysql> insert into ta values (null,true);Query OK, 1 row affected (0.06 sec)mysql> insert into ta values (null,true);ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'mysql> insert into ta values (null,false);Query OK, 1 row affected (0.06 sec)mysql> insert into ta values (null,false);Query OK, 1 row affected (0.08 sec)mysql> insert into ta values (null,true);ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'mysql> select * from ta;+------------+----------+| discountid | selected |+------------+----------+| 1 | 1 || 2 | 0 || 3 | 0 |+------------+----------+3 rows in set (0.00 sec)mysql> sql数据库和mysql数据库的差距有多大 大哥们请给于指点 MSSQL修改选中记录(未做同步保护):update TABLE set f=(case when ID=@id then 1 else 0 end) where ID=(select top 1 ID from TABLE where f=1) or ID=@id 用test1用户建了一个表A,再用test2用户去写表A,提示没有权限,问。。 [小问题6--删除表数据] 怎么求时间差? 平安夜给没收到礼物的GG &&MM 送分 -mysql版 自己写的一个建表语句,查不出毛病,但是在MySQL里执行不出来,大侠帮忙看下,解决就结贴在线等···· 如何选择数据库表类型 怎样获得数据库最近一次更新的时间 mysql中如何写一句与以下这个语句功能一样的sql 如何改变LONGBLOG字段的大小 端口问题! 日期比大小该怎么写? 某个字段自动取得这条记录中指定的字段的值?100
| discountid | rate | count | discount | selected |
+------------+------+-------+----------+----------+
| 1 | 0.8 | 0 | 0 | TRUE |
| 2 | 0.2 | 20 | 125.0 | TRUE |
| 3 | 0.9 | 50 | 300 | FALSE |
+------------+------+-------+----------+----------+在selected这个域中,只能有一个TRUE,可以有很多的FALSE。理解了么?
-> discountid int auto_increment primary key ,
-> `selected` BOOL
-> );
Query OK, 0 rows affected (0.09 sec)mysql>
mysql> delimiter |
mysql>
mysql> CREATE TRIGGER ta_bi BEFORE INSERT ON ta
-> FOR EACH ROW BEGIN
-> DECLARE bExist int default 0;
-> select count(*) into bExist from ta where selected;
-> if bExist>0 and new.selected then
-> set new.discountid=1;
-> end if;
-> END;
-> |
Query OK, 0 rows affected (0.08 sec)mysql>
mysql> delimiter ;
mysql> insert into ta values (null,true);
Query OK, 1 row affected (0.06 sec)mysql> insert into ta values (null,true);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into ta values (null,false);
Query OK, 1 row affected (0.06 sec)mysql> insert into ta values (null,false);
Query OK, 1 row affected (0.08 sec)mysql> insert into ta values (null,true);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> select * from ta;
+------------+----------+
| discountid | selected |
+------------+----------+
| 1 | 1 |
| 2 | 0 |
| 3 | 0 |
+------------+----------+
3 rows in set (0.00 sec)mysql>
update TABLE set f=(case when ID=@id then 1 else 0 end) where ID=(select top 1 ID from TABLE where f=1) or ID=@id