mysql - SQL最大记录数

Question

所以我有下表:

 CREATE TABLE Hospital_MedicalRecord(
        recNo CHAR(5),
        patient CHAR(9),
        doctor CHAR(9),
        enteredOn DATE NOT NULL,
        diagnosis VARCHAR(50) NOT NULL,
        treatment VARCHAR(50),
        PRIMARY KEY (recNo, patient),
        FOREIGN KEY (patient) REFERENCES Hospital_Patient(NINumber),
        FOREIGN KEY (doctor) REFERENCES Hospital_Doctor(NINumber)
    );

我想做到这样，单个患者的医疗记录绝不会超过 65,535 条。我应该做一个新的声明还是应该在上表中实现它。如果需要，我可以发布患者表。

Answer 1

您通常会为此使用 before insert 触发器，如果​​患者的记录数达到限制并尝试新插入，则会引发错误:

delimiter //

create trigger Trg_Hospital_MedicalRecord
before insert on Hospital_MedicalRecord
for each row
begin
    if (
        select count(*) from Hospital_MedicalRecord where patient = new.patient
    ) = 65535 then
            set msg = concat('Patient ', new.patient, ' cannot have more than 65535 records');
            signal state '45000' set message_text = msg;
    end if;
end
//

delimiter ;

我假设您不应允许在现有记录上更新患者。但如果这可能发生，那么您还需要一个 before update 触发器(使用完全相同的代码)。