MySQL报错：第3行列数与值数不匹配，请求排查解决

问题场景 #

执行下述SQL代码时，触发错误提示：Column count doesn't match value count at row 3。已知该错误由列数与值数不匹配引发，但自行检查表结构与插入语句后未定位到问题，需协助排查。

错误定位 #

问题出在Restaurant表的插入语句中：
原插入语句第三行的值仅包含8个元素，但Restaurant表定义了9个字段，缺失了Rest_Pmtid字段对应的值。

原错误代码片段：

INSERT INTO Restaurant(Rest_id, Rest_phone, Rest_name, Rest_street, Rest_city, Rest_state, Rest_zip, Rest_Pmtid, Rest_cuisine)
VALUES(1, 6788931568, "Paunch Burger", "6872 Lard Lane", "Pawnee", "IN", 46011, 4826, "Fast Food"),
  (2, 4569873185, "JJ's Diner", "23428 Main St,", "Pawnee", "IN", 46011, 78623, "Breakfast"),
  (3, 7862354862, "Mulligans", "6876 Classy Rd.", "Indianapolis", "IN", 46077, "Steakhouse"); -- 此处仅8个值，缺Rest_Pmtid

修正方案 #

为第三行补充Rest_Pmtid的数值（需符合整数类型要求），示例如下：

INSERT INTO Restaurant(Rest_id, Rest_phone, Rest_name, Rest_street, Rest_city, Rest_state, Rest_zip, Rest_Pmtid, Rest_cuisine)
VALUES(1, 6788931568, "Paunch Burger", "6872 Lard Lane", "Pawnee", "IN", 46011, 4826, "Fast Food"),
  (2, 4569873185, "JJ's Diner", "23428 Main St,", "Pawnee", "IN", 46011, 78623, "Breakfast"),
  (3, 7862354862, "Mulligans", "6876 Classy Rd.", "Indianapolis", "IN", 46077, 56789, "Steakhouse"); -- 补充Rest_Pmtid的值56789

完整可执行SQL #

CREATE TABLE Restaurant
  (Rest_id integer NOT NULL,
  Rest_phone integer NOT NULL,
  Rest_name VARCHAR(255),
  Rest_street VARCHAR(255),
  Rest_city VARCHAR(255),
  Rest_state VARCHAR(255),
  Rest_zip integer NOT NULL,
  Rest_Pmtid integer NOT NULL,
  Rest_cuisine VARCHAR(255),
  CONSTRAINT Restaurant_PK PRIMARY KEY (Rest_id));


CREATE TABLE Customer
  (Cust_id integer NOT NULL,
  Cust_phone integer NOT NULL,
  Cust_fname VARCHAR(255),
  Cust_lname VARCHAR(255),
  Cust_street VARCHAR(255),
  Cust_city VARCHAR(255),
  Cust_state VARCHAR(255),
  Cust_zip integer NOT NULL,
  Cust_Pmtid integer NOT NULL,
  CONSTRAINT Customer_PK PRIMARY KEY (Cust_id));


CREATE TABLE Driver
  (Driver_id integer NOT NULL,
  Driver_phone integer NOT NULL,
  Driver_fname VARCHAR(255),
  Driver_lname VARCHAR(255),
  Driver_license VARCHAR(255),
  Driver_plate VARCHAR(255),
  Driver_status VARCHAR(255),
  Driver_Pmtid integer NOT NULL,
  CONSTRAINT Driver_PK PRIMARY KEY (Driver_id));


CREATE TABLE Grub_Order
  (Rest_id integer NOT NULL,
  Cust_id integer NOT NULL,
  Order_time DATETIME,
  Order_id integer NOT NULL,
  Order_status VARCHAR(255),
  Driver_id integer NOT NULL,
  CONSTRAINT Grub_Order_PK PRIMARY KEY (Rest_id, Cust_id, Driver_id),
  CONSTRAINT Grub_Restaurant_FK FOREIGN KEY(Rest_id) REFERENCES Restaurant(Rest_id),
  CONSTRAINT Grub_Customer_FK FOREIGN KEY(Cust_id) REFERENCES Customer(Cust_id),
  CONSTRAINT Grub_Driver_FK FOREIGN KEY(Driver_id) REFERENCES Driver(Driver_id));

INSERT INTO Restaurant(Rest_id, Rest_phone, Rest_name, Rest_street, Rest_city, Rest_state, Rest_zip, Rest_Pmtid, Rest_cuisine)
VALUES(1, 6788931568, "Paunch Burger", "6872 Lard Lane", "Pawnee", "IN", 46011, 4826, "Fast Food"),
  (2, 4569873185, "JJ's Diner", "23428 Main St,", "Pawnee", "IN", 46011, 78623, "Breakfast"),
  (3, 7862354862, "Mulligans", "6876 Classy Rd.", "Indianapolis", "IN", 46077, 56789, "Steakhouse");


INSERT INTO Customer(Cust_id, Cust_phone, Cust_fname, Cust_lname, Cust_street, Cust_city, Cust_state, Cust_zip, Cust_Pmtid)
VALUES(1, 4025534397, "April", "Ludgate", "123 S 55 Ave.", "Omaha", "NE", 68132, 23456),
  (2, 2344325437, "Leslie", "Knope", "4387 Waffle Drive", "Pawnee", "IN", 46011, 98754),
  (3, 4569873265, "Ron", "Swanson", "987 Bacon Avenue", "Pawnee", "IN", 46011, 234789),
  (4, 0000000000, "Andy", "Dwyer", "2468 The Pit", "Pawnee", "IN", 46011, 12390);


INSERT INTO Driver(Driver_id, Driver_phone, Driver_fname, Driver_lname, Driver_license, Driver_plate, Driver_status, Driver_Pmtid)
VALUES(1, 2869372250, "Henry", "Roth", "C04790049", "IUC989", "Active", 444862),
  (2, 8156050336, "Charity", "Osborne", "D89973937", "REW222", "Active", 959227),
  (3, 9438936193, "Fritz", "Macias", "U06119817", "XUA177", "Active", 718371),
  (4, 5132849064, "Brenden", "Hill", "X22173227", "IOL455", "Active", 334452),
  (5, 9094778843, "Leah", "Peters", "V44276914", "AJA879", "Inactive", 603268);


INSERT INTO Grub_Order(Rest_id, Cust_id, Order_time, Order_id, Order_status, Driver_id)
VALUES(1, 1, "2020-09-30 6:44:00", 1, "out", 1),
  (1, 1, "2020-09-30 10:44:00", 2, "placed", 2),
  (2, 2, "2020-09-30 10:54:00", 3, "fulfilled", 3),
  (3, 3, "2020-09-30 10:46:00", 4, "placed", 4),
  (2, 4, "2020-09-30 11:44:00", 5, "cancelled", 1);

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