MySQL条件插入与动态值 [英] MySql conditional Insert with dynamic values

问题描述

因此，在我解释我的问题之前，这里有一些简化的表定义以帮助说明我的问题:

So before I explain my question, Here are a couple stripped down table definitions to help illustrate my issue:

-- Holds data about different memberships
CREATE TABLE IF NOT EXISTS `Member_Types` ( 
`ID` INT UNSIGNED NOT NULL AUTO_INCREMENT, 
`Name` VARCHAR(20) NOT NULL,  
`Description` VARCHAR(255) NOT NULL, 
`Member_Limit` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0', 
PRIMARY KEY( `ID` )
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `Member_Types` ( `ID`, `Name`, `Description`, `Member_Limit` ) VALUES 
( 1, 'General Member', 'Description of this membership.', 00 ), 
( 2, 'Extended Member', 'Description of this membership.', 00 ), 
( 3, 'Premium Member', 'Description of this membership.', 00), 
( 4, 'General Administrator', 'Description of this membership.', 05 ), 
( 5, 'Master Administrator', 'Description of this membership.', 01 );

-- Stores the basic data about our site members
CREATE TABLE IF NOT EXISTS `Member_Infos` ( 
`ID` BIGINT(8) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT, 
`Username` VARCHAR(30) NOT NULL UNIQUE, 
`Password` CHAR(41) NOT NULL, 
`EmailAddr` VARCHAR(100) NOT NULL, 
`Type_ID` INT UNSIGNED NOT NULL, 
`Salt_ID` BIGINT(8) UNSIGNED ZEROFILL NOT NULL, 
PRIMARY KEY( `ID` ), 
FOREIGN KEY( `Type_ID` ) REFERENCES `Member_Types` ( `ID` )
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Member_Infos表包含一个绑定Member_Infos.Type_ID = Member_Types.ID的外键

The Member_Infos table contains a foreign key that binds Member_Infos.Type_ID = Member_Types.ID

Member_Types表具有一个Member_Limit列，该列包含一个整数，该整数表示Member_Infos表的最大记录数 可以包含其Type_ID等于Member_Types.ID的地方

The Member_Types table has a column Member_Limit which contains an integer that represents the maximum number of records that the Member_Infos table can contain where its Type_ID is equal to Member_Types.ID

我能够写一个检查约束，但是显然mysql没有检查约束. 我想编写一个触发器，在插入之前检查该member_infos表中的记录数是否< = Member_Types.Member_Limit. 例如:使用上面的数据，记录4的Member_Limit =5.如果尝试将新记录插入到Member_Infos表中， 的Type_ID = 4，并且Member_Infos表中的记录计数(Type_ID <= 5)大于插入的数据，否则将被拒绝. 任何建议将不胜感激.

I was able to write a check constraint but apparently mysql doesn't enfore check constraints. I would like to write a trigger that checks that the count of records in the member_infos table is <= the Member_Types.Member_Limit before inserting. for example: using the data above, record 4 has a Member_Limit = 5. If a new record is attempted to be inserted into the Member_Infos table that has a Type_ID = 4 and a count of records in the Member_Infos table with Type_ID <= 5 than the data is inserted, otherwise it is rejected. Any advice would be greatly appreciated.

推荐答案

代替触发器，您可以编写自己的普通查询以在插入之前检查约束".试试:

Instead of a trigger you could write your own plain query to check the "constraints" before insert. Try:

INSERT INTO member_infos
SELECT      1, 'Timothy', 'secret', '[e-mail]', 5, 0
FROM        dual
WHERE       (SELECT COUNT(*) FROM member_infos WHERE Type_ID = 5) 
            < 
            (SELECT Member_Limit FROM member_types WHERE ID = 5)

我曾经检查Type_ID = 5的情况.这会忽略是否不满足计数标准，并且仅当member_info中id = 5类型的条目的成员计数小于您的<中设置的限制时才插入c1>表格

I have used to check in case of Type_ID = 5. This ignores if count criterion is not met and inserts only if count of member of entries in member_info with type id = 5 is less than the limit set in your member_types table

这篇关于MySQL条件插入与动态值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！