我正在尝试根据插入的插入id设置变量。所以我写了:
INSERT INTO person (first_name, last_name, middle_names, suffix, title)
VALUES ("Frank", "Thring", "", "", "Mr");
SET @person_frank_thring_id = LAST_INSERT_ID();
INSERT INTO address (line2, line3, postcode, state, suburb)
VALUES ("Dock 3", "22 Boundary Rd", 2088, "NSW", "Mascot");
SET @address_franks_aircraft_maintenance_id = LAST_INSERT_ID();
INSERT INTO contact (email, phone)
VALUES ("info@franks.com", "0245732552");
SET @contact_franks_aircraft_maintenance_id = LAST_INSERT_ID();
INSERT INTO maintainer (abn, address_id, contact_id, nk, name, person_id)
VALUES (73507986550, @address_franks_aircraft_maintenance_id, @contact_franks_aircraft_maintenance_id,
"c49439a4-a24a-4e1b-bc92-ebad6caf5e74", "Frank's Airplane Repairs", @person_frank_thring_id);
SET @maintainer_franks_maint_id = LAST_INSERT_ID();
INSERT INTO approved_process (nk, approval_number, description, expires, maintainer_id)
VALUES ("ee114aab-d201-498d-85a4-2b95e9df8b3d", "13226A", "Welding and Heat Treatment", "2021-05-02", @maintainer_franks_maint_id);
person、address、contact和maintainer都是预先存在的表。我单独运行了前三个插入,并检查了数据是否插入到表中。当我运行整个程序块时,我得到一个非常普遍的错误:
[查询73中的错误]您的sql语法有错误;检查与您的mysql服务器版本相对应的手册,以获得在第2行的“@maintener\u franks\u maint\u id”附近使用的正确语法执行停止!
编辑-请参见下面的删除和创建语句:
DROP TABLE IF EXISTS address;
DROP TABLE IF EXISTS contact;
DROP TABLE IF EXISTS person;
DROP TABLE IF EXISTS maintainer;
CREATE TABLE person (
id BIGINT NOT NULL AUTO_INCREMENT,
first_name varchar(255),
last_name varchar(255),
middle_names varchar(255),
suffix varchar(255),
title varchar(255),
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE address (
id BIGINT NOT NULL AUTO_INCREMENT,
line1 varchar(255),
line2 varchar(255),
line3 varchar(255),
postcode integer,
state varchar(255),
suburb varchar(255),
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE contact (
id BIGINT NOT NULL AUTO_INCREMENT,
email varchar(255),
fax varchar(255),
mobile varchar(255),
phone varchar(255),
address_id bigint,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE maintainer (
id BIGINT NOT NULL AUTO_INCREMENT,
abn bigint,
image varchar(255),
nk varchar(255),
name varchar(255),
address_id bigint,
contact_id bigint,
person_id bigint,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE maintainer
ADD CONSTRAINT UK_maintainer_nk unique (nk);
ALTER TABLE maintainer
ADD CONSTRAINT FKs7jo395jusgm3631g7w845wy4 FOREIGN KEY (address_id) REFERENCES address (id);
ALTER TABLE maintainer
ADD CONSTRAINT FKgixmfq21peg70qtff3q4ktq1 FOREIGN KEY (contact_id) REFERENCES contact (id);
ALTER TABLE maintainer
ADD CONSTRAINT FK86boj3163qysduc7x3a2m84mh FOREIGN KEY (person_id) REFERENCES person (id);
新增审批+流程:
CREATE TABLE approved_process (
id BIGINT NOT NULL AUTO_INCREMENT,
nk varchar(255),
approval_number varchar(255),
description varchar(255),
expires date,
maintainer_id bigint,
subcontractor_id bigint,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
我希望这能澄清问题。
1条答案
按热度按时间efzxgjgh1#
抱歉,各位,这似乎是用户在某种程度上的错误。有两个问题:一个是一个奇怪的看不见的字符在开始的时候
INSERT INTO approved_process
而且,由于某种原因,sequel pro无法区分@franks
在email值和@franks变量声明的开头。