mysql触发器部分工作

Question

我的应用程序需要 5 种不同类别的车辆，每种车辆都有一些公共(public)字段。所以我所做的是为 5 类车辆中的每一种创建 5 个表 vehicle1、vehicle2、vehicle3、vehicle4,车辆 5然后创建了第 6 个表“车辆”，用于存储每辆车共有的字段。现在，每当我输入与特定车辆相关的信息(INSERT INTO 该特定车辆的类别表)时，都会执行一个触发器，将公共(public)字段插入到 vehicle 表中。所以触发器看起来像这样

CREATE TRIGGER `tr_vehicle1_info` AFTER INSERT ON `vehicle1`
 FOR EACH ROW insert into vehicle(categ,year,make,model,vin,user_id,principal_driver) values (1,new.year,new.make,new.model,new.vin,new.user_id,new.principal_driver) 

CREATE TRIGGER `tr_vehicle1_info` AFTER INSERT ON `vehicle2`
 FOR EACH ROW insert into vehicle(categ,year,make,model,vin,user_id,principal_driver) values (2,new.year,new.make,new.model,new.vin,new.user_id,new.principal_driver)

CREATE TRIGGER `tr_vehicle1_info` AFTER INSERT ON `vehicle3`
 FOR EACH ROW insert into vehicle(categ,year,make,model,vin,user_id,principal_driver) values (3,new.year,new.make,new.model,new.vin,new.user_id,new.principal_driver)

等等......

现在的问题是，当我为车辆插入信息时，触发器会执行，并且值会插入到表 vehicle 中，但对于 categ 字段，vehicle 表始终插入 0。 categ 字段的类型是 tinyint(1)。

我不明白怎么了。帮助？

更新

车辆结构

CREATE TABLE IF NOT EXISTS `vehicle` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `categ` tinyint(1) NOT NULL,
  `year` char(4) NOT NULL,
  `make` varchar(30) NOT NULL,
  `model` varchar(50) NOT NULL,
  `vin` varchar(25) NOT NULL,
  `user_id` int(11) NOT NULL,
  `principal_driver` int(11) DEFAULT NULL,
  `secondary_driver` varchar(30) NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `vin` (`vin`,`user_id`)
) ENGINE=InnoDB;

Answer 1

您的类别定义为单个位:“TINYINT(1)”分配 1 位来存储整数。所以你只能在其中存储一个 0 或一个 1。 (编辑:我对存储分配有误，我误解了文档。)但老实说，我不明白你为什么要“倒着”输入信息。如果您想避免一堆空条目，我会将信息输入到主车辆表中，然后将记录链接到包含特定于车辆类别的列的表中——通常我只是根据信息类型重新调整空字段的用途节省空间(如果我不打算通过该信息进行太多搜索，如果有的话)并且只检索我需要的内容。但我不知道你想要完成什么，所以我不能肯定地说。

编辑:它有效吗？如果无效，您遇到了什么问题？这是我可能会做的(注意:不完整，也没有检查):

        CREATE  TABLE IF NOT EXISTS `logistics`.`vehicle` (
          `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
          `category` TINYINT(4) NOT NULL COMMENT '(4) Allows for 7 vehicle Categories' ,
          `v_year` YEAR NOT NULL ,
          `v_make` VARCHAR(30) NOT NULL ,
          `created` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
          `modified` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ,
          PRIMARY KEY (`id`) )
        ENGINE = InnoDB;

            CREATE  TABLE IF NOT EXISTS `logistics`.`driver` (
          `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
          `first_name` VARCHAR(45) NOT NULL ,
          `middle_name` VARCHAR(45) NULL COMMENT 'helpful in cases of 2 drivers with the exact same first and last' ,
          `sir_name` VARCHAR(45) NOT NULL ,
          `suffix_name` VARCHAR(45) NULL COMMENT 'rather than \"pollute\" your sir name with a suffix' ,
          `license_num` VARCHAR(45) NOT NULL COMMENT 'Always handy in case of claims, reporting, and checking with the DMV, etc.' ,
          `license_expiration` DATE NOT NULL COMMENT 'Allows status of driver\'s license report to be run and alert staff of needed to verify updated license' ,
          `license_class` CHAR(1) NULL COMMENT 'From what I know classes are \'A\' through \'D\' and usually a single letter. Helpful if needing to assign drivers to vehicles.' ,
          `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
          `modified` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ,
          PRIMARY KEY (`id`) )
        ENGINE = InnoDB;
            CREATE  TABLE IF NOT EXISTS `logistics`.`driver_vehicle` (
          `vehicle_id` INT(11) UNSIGNED NOT NULL ,
          `driver_id` INT(11) UNSIGNED NOT NULL ,
          `principal_driver` TINYINT(1) NOT NULL DEFAULT 'FALSE' COMMENT 'if not specified it will be assumed the driver is not a primary.' ,
          `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
          `modified` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ,
          `admin_id` INT(11) UNSIGNED NOT NULL ,
          PRIMARY KEY (`vehicle_id`, `driver_id`) ,
          INDEX `fk_driver_vehicle_driver1` (`driver_id` ASC) ,
          CONSTRAINT `fk_driver_vehicle_vehicle`
            FOREIGN KEY (`vehicle_id` )
            REFERENCES `mydb`.`vehicle` (`id` )
            ON DELETE CASCADE
            ON UPDATE CASCADE,
          CONSTRAINT `fk_driver_vehicle_driver1`
            FOREIGN KEY (`driver_id` )
            REFERENCES `mydb`.`driver` (`id` )
            ON DELETE CASCADE
            ON UPDATE CASCADE)
        ENGINE = InnoDB;

        CREATE  TABLE IF NOT EXISTS `logistics`.`vehicle_options` (
  `vehicle_id` INT(11) UNSIGNED NOT NULL ,
  `option_type` VARCHAR(45) NOT NULL COMMENT 'if certain options are common you could pull by type of option i.e. cosmetic, cargo, hp, weight_capacity, max_speed, etc.' ,
  `option_value` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`vehicle_id`, `option_type`) ,
  CONSTRAINT `fk_vehicle_options_vehicle1`
    FOREIGN KEY (`vehicle_id` )
    REFERENCES `mydb`.`vehicle` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;