MySQL @local_variable 不能连续工作

Question

在使用临时变量进行研发时，我遇到了数据库的异常行为。为了充分说明我的问题，我将共享表架构及其数据。

这些是我的表格:

CREATE TABLE category (
  categoryId INT PRIMARY KEY AUTO_INCREMENT,
  categoryName VARCHAR(100) NOT NULL UNIQUE,
  categoryFlag TINYINT NOT NULL DEFAULT 0
);

CREATE TABLE object (
  objectId INT PRIMARY KEY AUTO_INCREMENT,
  objectName VARCHAR(100) NOT NULL UNIQUE,
  objectFlag TINYINT NOT NULL DEFAULT 0
);

CREATE TABLE mapping(
  mapId INT PRIMARY KEY AUTO_INCREMENT,
  catId INT NOT NULL,
  objId INT NOT NULL,
  mapFlag TINYINT NOT NULL DEFAULT 0,
  CONSTRAINT UC_mapping_cat_obj UNIQUE (catId,objId)
);

这些是我要插入表中的记录:

INSERT INTO category(categoryName) VALUES ('Beverage');
INSERT INTO category(categoryName) VALUES ('Dairy Product');
INSERT INTO category(categoryName) VALUES ('Desert');
INSERT INTO category(categoryName) VALUES ('Digestive Food');
INSERT INTO category(categoryName) VALUES ('Fruit');
INSERT INTO category(categoryName) VALUES ('Herb');
INSERT INTO category(categoryName) VALUES ('Preservative');
INSERT INTO category(categoryName) VALUES ('Spice');
INSERT INTO category(categoryName) VALUES ('Vegetable');

INSERT INTO object(objectName) VALUES ('Apple');
INSERT INTO object(objectName) VALUES ('Butter');
INSERT INTO object(objectName) VALUES ('Cabbage');
INSERT INTO object(objectName) VALUES ('Cake');
INSERT INTO object(objectName) VALUES ('Chili');
INSERT INTO object(objectName) VALUES ('Clove');
INSERT INTO object(objectName) VALUES ('Cinnamon');
INSERT INTO object(objectName) VALUES ('Coffee');
INSERT INTO object(objectName) VALUES ('Coriander');
INSERT INTO object(objectName) VALUES ('Curd');
INSERT INTO object(objectName) VALUES ('Curry Leaves');
INSERT INTO object(objectName) VALUES ('Green Tea');
INSERT INTO object(objectName) VALUES ('Holy Basil');
INSERT INTO object(objectName) VALUES ('Ice Cream');
INSERT INTO object(objectName) VALUES ('Indian Gooseberry');
INSERT INTO object(objectName) VALUES ('Lemon');
INSERT INTO object(objectName) VALUES ('Milk');
INSERT INTO object(objectName) VALUES ('Spinach');
INSERT INTO object(objectName) VALUES ('Turmeric');
INSERT INTO object(objectName) VALUES ('Tea');

INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Beverage' AND objectName = 'Coffee';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Beverage' AND objectName = 'Green Tea';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Beverage' AND objectName = 'Tea';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Dairy Product' AND objectName = 'Butter';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Dairy Product' AND objectName = 'Curd';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Dairy Product' AND objectName = 'Ice Cream';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Dairy Product' AND objectName = 'Milk';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Desert' AND objectName = 'Ice Cream';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Desert' AND objectName = 'Cake';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Digestive Food' AND objectName = 'Curd';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Digestive Food' AND objectName = 'Indian Gooseberry';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Digestive Food' AND objectName = 'Lemon';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Fruit' AND objectName = 'Apple';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Fruit' AND objectName = 'Indian Gooseberry';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Fruit' AND objectName = 'Lemon';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Herb' AND objectName = 'Clove';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Herb' AND objectName = 'Cinnamon';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Herb' AND objectName = 'Coriander';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Herb' AND objectName = 'Holy Basil';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Herb' AND objectName = 'Turmeric';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Preservative' AND objectName = 'Clove';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Preservative' AND objectName = 'Cinnamon';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Preservative' AND objectName = 'Lemon';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Spice' AND objectName = 'Chili';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Spice' AND objectName = 'Cinnamon';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Spice' AND objectName = 'Curry Leaves';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Spice' AND objectName = 'Holy Basil';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Spice' AND objectName = 'Turmeric';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Vegetable' AND objectName = 'Cabbage';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Vegetable' AND objectName = 'Coriander';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Vegetable' AND objectName = 'Spinach';

连接映射表与类别和对象表的查询:

SET @catName='';
SELECT
  CASE WHEN @catName=categoryName THEN '' ELSE @catName:=categoryName END AS catName, 
  objectName
FROM mapping
INNER JOIN category ON catId = categoryId
INNER JOIN object ON objId = objectId
ORDER BY categoryName, objectName ;

Screenshot highlighting the problem

正如您在图片中看到的，我突出显示了应该显示值“Preservative”但显示“Spice”的行，而正下方的行必须显示值“Spice”但显示“Preservative”。

我无法弄清楚这种异常行为的原因。我试图改变内连接表的顺序，但没有用。加入“类别”表或“对象”表似乎也不起作用。

我能够想出可以重现此问题的序列。

-- Insert in -> category
INSERT INTO category(categoryName) VALUES ('Digestive Food');
INSERT INTO category(categoryName) VALUES ('Beverage');
INSERT INTO category(categoryName) VALUES ('Fruit');
INSERT INTO category(categoryName) VALUES ('Desert');
INSERT INTO category(categoryName) VALUES ('Vegetable');
INSERT INTO category(categoryName) VALUES ('Preservative');
INSERT INTO category(categoryName) VALUES ('Dairy Product');
INSERT INTO category(categoryName) VALUES ('Spice');
INSERT INTO category(categoryName) VALUES ('Herb');



-- Insert in -> object
INSERT INTO object(objectName) VALUES ('Cake');
INSERT INTO object(objectName) VALUES ('Turmeric');
INSERT INTO object(objectName) VALUES ('Chili');
INSERT INTO object(objectName) VALUES ('Tea');
INSERT INTO object(objectName) VALUES ('Coriander');
INSERT INTO object(objectName) VALUES ('Apple');
INSERT INTO object(objectName) VALUES ('Butter');
INSERT INTO object(objectName) VALUES ('Cabbage');
INSERT INTO object(objectName) VALUES ('Spinach');
INSERT INTO object(objectName) VALUES ('Ice Cream');
INSERT INTO object(objectName) VALUES ('Clove');
INSERT INTO object(objectName) VALUES ('Milk');
INSERT INTO object(objectName) VALUES ('Coffee');
INSERT INTO object(objectName) VALUES ('Green Tea');
INSERT INTO object(objectName) VALUES ('Holy Basil');
INSERT INTO object(objectName) VALUES ('Cinnamon');
INSERT INTO object(objectName) VALUES ('Curry Leaves');
INSERT INTO object(objectName) VALUES ('Lemon');
INSERT INTO object(objectName) VALUES ('Curd');
INSERT INTO object(objectName) VALUES ('Indian Gooseberry');



-- Insert in -> mapping
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Dairy Product' AND objectName = 'Butter';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Spice' AND objectName = 'Holy Basil';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Herb' AND objectName = 'Cinnamon';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Herb' AND objectName = 'Coriander';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Dairy Product' AND objectName = 'Ice Cream';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Desert' AND objectName = 'Cake';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Desert' AND objectName = 'Ice Cream';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Fruit' AND objectName = 'Lemon';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Spice' AND objectName = 'Cinnamon';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Digestive Food' AND objectName = 'Curd';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Dairy Product' AND objectName = 'Curd';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Vegetable' AND objectName = 'Spinach';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Herb' AND objectName = 'Holy Basil';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Spice' AND objectName = 'Curry Leaves';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Digestive Food' AND objectName = 'Indian Gooseberry';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Digestive Food' AND objectName = 'Lemon';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Fruit' AND objectName = 'Apple';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Fruit' AND objectName = 'Indian Gooseberry';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Preservative' AND objectName = 'Lemon';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Vegetable' AND objectName = 'Cabbage';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Herb' AND objectName = 'Clove';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Beverage' AND objectName = 'Tea';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Herb' AND objectName = 'Turmeric';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Vegetable' AND objectName = 'Coriander';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Preservative' AND objectName = 'Clove';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Dairy Product' AND objectName = 'Milk';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Beverage' AND objectName = 'Green Tea';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Spice' AND objectName = 'Chili';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Preservative' AND objectName = 'Cinnamon';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Spice' AND objectName = 'Turmeric';
INSERT INTO mapping (catId,objId) SELECT categoryId, objectId FROM category JOIN object WHERE categoryName = 'Beverage' AND objectName = 'Coffee';



-- Select Query
SET @catName='';
SELECT
  categoryName,
  CASE WHEN @catName=categoryName THEN '' ELSE @catName:=categoryName END AS catName, 
  objectName
FROM mapping
INNER JOIN category ON catId = categoryId
INNER JOIN object ON objId = objectId
ORDER BY categoryName, objectName ;

Answer 1

MySQL 可能对变量很挑剔。老实说，我在 GROUP BY 中看到过这个问题，但在 ORDER BY 中没有看到。您可以使用子查询修复它:

SELECT categoryname,
       CASE WHEN @catName=categoryName THEN '' ELSE @catName:=categoryName END AS catName, 
       objectName
FROM (SELECT categoryname, objectname
      FROM mapping INNER JOIN
           category
           ON catId = categoryId INNER JOIN
           object
           ON objId = objectId
      ORDER BY categoryName, objectName
     ) co CROSS JOIN
     (SELECT @catName = '') params;

Here是一个 SQL fiddle 。