mysql 动态添加条件,MySQL条件插入动态值

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;

The Member_Infos table contains a foreign key that binds 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

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', 'me@myself.com', 5, 0

FROM dual

WHERE (SELECT COUNT(*) FROM member_infos WHERE Type_ID = 5)

<

(SELECT Member_Limit FROM member_types WHERE ID = 5)

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