MySQL插入company表遇1054错误：提示未知列company_name（列实际存在）

问题背景 #

我在MySQL中开发ERD模型，通过反复删除架构并执行正向工程测试模型更新（暂未掌握模型同步操作），此前所有操作均正常。后续使用MySQL/ODBC Connector连接Microsoft Access作为前端，系统运行也无异常。但近期执行以下INSERT语句时：

INSERT INTO company(company_name, address, city_id, zip_code,
country_code, kind_of_organization_id, number_of_employees, total_asset, 
total_liability, registration_body_id, registration_date) 
VALUES
('Astro Planet Pictures', '90210 Rodeo Ave', 
(SELECT city_id FROM city WHERE name = 'Athens'), 
46543,
(SELECT code FROM country WHERE country.name = 'Greece'), 
(SELECT id FROM kind_of_organization WHERE name = 'Non Profit'), 
45,
393430.4, 98345.5, 
(SELECT r.id FROM registration_body r, country c WHERE r.country_code = c.code AND c.name = 'Slovakia'), 
'2020-01-04');

突然出现报错：

Error Code: 1054: Unknown column 'company_name' in 'field list'

company_name列从项目启动就存在，且此前INSERT操作从未出现问题。已断开Microsoft Access与MySQL的连接，单独测试MySQL问题依旧；尝试将列名改为name（怀疑存在不可见字符），也未解决问题，目前排查陷入僵局。

相关表结构与触发器 #

company表DDL：

CREATE TABLE IF NOT EXISTS `movie_production_companies`.`company` (
  `id` VARCHAR(144) NOT NULL,
  `company_name` VARCHAR(144) NOT NULL,
  `address` VARCHAR(115) NOT NULL,
  `zip_code` INT NOT NULL,
  `city_id` INT NOT NULL,
  `country_code` CHAR(3) NOT NULL,
  `kind_of_organization_id` INT NOT NULL,
  `number_of_employees` INT NOT NULL,
  `total_asset` DECIMAL(10,2) NOT NULL,
  `total_liability` DECIMAL(10,2) NOT NULL,
  `registration_body_id` INT NOT NULL,
  `registration_date` DATE NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `country_id_idx` (`country_code` ASC) VISIBLE,
  INDEX `city_id_idx` (`city_id` ASC) VISIBLE,
  INDEX `kind_of_organization_fk_idx` (`kind_of_organization_id` ASC) VISIBLE,
  INDEX `regulatory_body_fk_idx` (`registration_body_id` ASC) VISIBLE,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) VISIBLE,
  CONSTRAINT `company_country_fk`
    FOREIGN KEY (`country_code`)
    REFERENCES `movie_production_companies`.`country` (`code`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `company_city_fk`
    FOREIGN KEY (`city_id`)
    REFERENCES `movie_production_companies`.`city` (`city_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `company_kind_of_organization_fk`
    FOREIGN KEY (`kind_of_organization_id`)
    REFERENCES `movie_production_companies`.`kind_of_organization` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `company_regulatory_body_fk`
    FOREIGN KEY (`registration_body_id`)
    REFERENCES `movie_production_companies`.`registration_body` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

关联触发器（已恢复原始格式）：

CREATE DEFINER = CURRENT_USER TRIGGER `movie_production_companies`.`company_BEFORE_INSERT_pk_maker` 
BEFORE INSERT ON `company` 
FOR EACH ROW
BEGIN
    DECLARE space_count INT;
    
    SET space_count = LENGTH(company_name) - LENGTH(REPLACE(company_name, ' ', ''));
    
    IF space_count = 0 THEN
        SET NEW.id =  CONCAT(
            NEW.country_code, '-', 
            UPPER(LEFT(NEW.company_name, 3)), '-',
            FLOOR(100000 + RAND() * 900000)
        );
    ELSEIF space_count = 1 THEN
        SET NEW.id = CONCAT(
            NEW.country_code, '-', 
            UPPER(LEFT(NEW.company_name, 1)), 
            UPPER(LEFT(SUBSTRING_INDEX(NEW.company_name, ' ', -1), 2)),
            FLOOR(100000 + RAND() * 900000)
        );
    ELSE
        SET NEW.id = CONCAT(
        NEW.country_code, '-', 
        UPPER(LEFT(NEW.company_name, 1)), 
        UPPER(LEFT(SUBSTRING_INDEX(SUBSTRING_INDEX(NEW.company_name, ' ', 2), ' ', -1), 1)),
        UPPER(LEFT(SUBSTRING_INDEX(SUBSTRING_INDEX(NEW.company_name, ' ', 3), ' ', -1), 1)),
        FLOOR(100000 + RAND() * 900000)
        );
    END IF;
END;

问题根源 #

触发器中计算space_count的语句直接使用了company_name，没有添加NEW.前缀。在BEFORE INSERT触发器中，引用新插入行的字段必须使用NEW.前缀，否则MySQL会将其解析为表的现有列名，导致触发"Unknown column"错误。

修复方案 #

修改触发器中所有直接引用company_name的地方，添加NEW.前缀，修改后的触发器代码如下：

CREATE DEFINER = CURRENT_USER TRIGGER `movie_production_companies`.`company_BEFORE_INSERT_pk_maker` 
BEFORE INSERT ON `company` 
FOR EACH ROW
BEGIN
    DECLARE space_count INT;
    
    -- 添加NEW.前缀引用新行的company_name字段
    SET space_count = LENGTH(NEW.company_name) - LENGTH(REPLACE(NEW.company_name, ' ', ''));
    
    IF space_count = 0 THEN
        SET NEW.id =  CONCAT(
            NEW.country_code, '-', 
            UPPER(LEFT(NEW.company_name, 3)), '-',
            FLOOR(100000 + RAND() * 900000)
        );
    ELSEIF space_count = 1 THEN
        SET NEW.id = CONCAT(
            NEW.country_code, '-', 
            UPPER(LEFT(NEW.company_name, 1)), 
            UPPER(LEFT(SUBSTRING_INDEX(NEW.company_name, ' ', -1), 2)),
            FLOOR(100000 + RAND() * 900000)
        );
    ELSE
        SET NEW.id = CONCAT(
        NEW.country_code, '-', 
        UPPER(LEFT(NEW.company_name, 1)), 
        UPPER(LEFT(SUBSTRING_INDEX(SUBSTRING_INDEX(NEW.company_name, ' ', 2), ' ', -1), 1)),
        UPPER(LEFT(SUBSTRING_INDEX(SUBSTRING_INDEX(NEW.company_name, ' ', 3), ' ', -1), 1)),
        FLOOR(100000 + RAND() * 900000)
        );
    END IF;
END;

操作步骤 #

1. 先删除原有触发器：

DROP TRIGGER IF EXISTS movie_production_companies.company_BEFORE_INSERT_pk_maker;

2. 执行修改后的触发器创建语句
3. 重新运行原INSERT语句，即可正常执行

内容的提问来源于stack exchange，提问作者chik0di