MySQL SELECT没有找到已知值

Question

这里可能有一些简单的东西我只是没有看到，但我已经尝试了我能想到的所有几个小时。是时候把它扔给更有知识的人了……

我想尝试拦截一个重复的索引，所以我的自动增量值不会前进。而且，过了这么久，这已经成为我现在必须解决的问题之一了。

从一个空表开始，为了调用INSERT逻辑，我将PricingHeaderID_In设置为零来调用这个过程。

Lookup1显然找不到任何东西，因为没有要找的东西，而且pricingHeaderID1为NULL。然后，插入新行，Lookup2按预期检索新插入的行，pricingHeaderID2显示正确的值。

现在，我在各处用完全相同的值调用过程，Lookup1仍然找不到任何东西，pricingHeaderID1为NULL。这导致插入逻辑运行，MySQL正确地检测到重复。

我的问题很简单:如果MySQL可以检测到值将导致重复键，那么为什么Lookup1失败?

下面是我的表定义:

CREATE TABLE PricingHeaders (
PricingHeaderID bigint NOT NULL AUTO_INCREMENT,
UserID bigint NOT NULL,
ClientName varchar(100) NOT NULL,
Subdivision varchar(100) NOT NULL,
SubjectProperty varchar(100) NOT NULL,
DatePrepared date NOT NULL,
MonthsSearched smallint DEFAULT NULL,
Bedrooms tinyint DEFAULT NULL,
Bathrooms tinyint DEFAULT NULL,
Stories tinyint DEFAULT NULL,
SquareFeet smallint DEFAULT NULL,
YearBuilt smallint DEFAULT NULL,
HasPool bit DEFAULT 0,
Created  datetime DEFAULT CURRENT_TIMESTAMP,
Modified  datetime DEFAULT CURRENT_TIMESTAMP  ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (PricingHeaderID),
UNIQUE KEY UserClientPropertyDate (UserID,ClientName,SubjectProperty,DatePrepared),
INDEX UserSubdivision (UserID,Subdivision)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

这是我的存储过程:

CREATE PROCEDURE SavePricingHeader ( Token_In VARCHAR(36),
PricingHeaderID_In bigint,
UserID_In bigint,
ClientName_In varchar(100),
Subdivision_In varchar(100),
SubjectProperty_In varchar(100),
DatePrepared_In date,
MonthsSearched_In smallint,
Bedrooms_In tinyint,
Bathrooms_In tinyint,
Stories_In tinyint,
SquareFeet_In smallint,
YearBuilt_In smallint,
HasPool_In bit   )
BEGIN
DECLARE errNo int DEFAULT 0;
DECLARE errMsg varchar(250) DEFAULT 'OK';
DECLARE currOp varchar(250) DEFAULT 'Init';
DECLARE pricingHeaderID1 bigint DEFAULT NULL;
DECLARE pricingHeaderID2 bigint DEFAULT NULL;
DECLARE tokenValidated bit DEFAULT 0;
DECLARE sqlErrNo CHAR(5) DEFAULT '00000';
DECLARE sqlErrMsg TEXT DEFAULT '';

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1 sqlErrNo = RETURNED_SQLSTATE, sqlErrMsg = MESSAGE_TEXT;
SET errNo = 1;
SET errMsg = 'SQL Error';
END;

IF errNo = 0 THEN
SET currOp = 'Extend session';
CALL ExtendSession(Token_In, tokenValidated);
END IF;

IF errNo = 0 THEN
IF tokenValidated = 0 THEN
SET errNo = 9001;
SET errMsg = 'Invalid session.';
END IF;
END IF;

IF errNo = 0 THEN
SET currOp = 'Lookup1';
SELECT `PricingHeaderID` INTO pricingHeaderID1 FROM `PricingHeaders` 
WHERE `UserID` = UserID_In
AND `ClientName` = ClientName_In
AND `SubjectProperty` = SubjectProperty_In
AND `DatePrepared` = DatePrepared_In;
IF errNo = 0 THEN
IF pricingHeaderID1 IS NOT NULL THEN
IF pricingHeaderID1 <> PricingHeaderID_In THEN
SET errNo = 9002;
SET errMsg = 'Duplicate Index.';
END IF;
END IF;
END IF;
END IF;

IF errNo = 0 THEN
IF PricingHeaderID_In = 0 THEN
SET currOp = 'Insert';
INSERT INTO `PricingHeaders` (
`UserID`,
`ClientName`,
`Subdivision`,
`SubjectProperty`,
`DatePrepared`,
`MonthsSearched`,
`Bedrooms`,
`Bathrooms`,
`Stories`,
`SquareFeet`,
`YearBuilt`,
`HasPool`
) VALUES (
UserID_In,
ClientName_In,
Subdivision_In,
SubjectProperty_In,
DatePrepared_In,
MonthsSearched_In,
Bedrooms_In,
Bathrooms_In,
Stories_In,
SquareFeet_In,
YearBuilt_In,
HasPool_In
);
IF errNo = 0 THEN
SET pricingHeaderID1 = LAST_INSERT_ID();
END IF;
ELSE        
SET currOp = 'Update';
SET pricingHeaderID1 = PricingHeaderID_In;
UPDATE `PricingHeaders` SET
`ClientName` = ClientName_In,
`Subdivision` = Subdivision_In,
`SubjectProperty` = SubjectProperty_In,
`DatePrepared` = DatePrepared_In,
`MonthsSearched` = MonthsSearched_In,
`Bedrooms` = Bedrooms_In,
`Bathrooms` = Bathrooms_In,
`Stories` = Stories_In,
`SquareFeet` = SquareFeet_In,
`YearBuilt` = YearBuilt_In,
`HasPool` = HasPool_In
WHERE `PricingHeaderID` = PricingHeaderID_In;
END IF;
END IF;
IF errNo = 0 THEN
SET currOp = 'Lookup2';
SELECT `PricingHeaderID` INTO pricingHeaderID2 FROM `PricingHeaders` 
WHERE `UserID` = UserID_In
AND `ClientName` = ClientName_In
AND `SubjectProperty` = SubjectProperty_In
AND `DatePrepared` = DatePrepared_In;
END IF;

IF errNo = 0 THEN
IF pricingHeaderID2 IS NULL THEN
SET errNo = 9003;
SET errMsg = 'Not retrieved.';
END IF;
END IF;

SELECT pricingHeaderID1, pricingHeaderID2, errNo, errMsg, currOp, sqlErrNo, sqlErrMsg;

END

Answer 1

你的基本问题是你对第二个过程的调用

必须看起来像

CALL ExtendSession(Token_In, @tokenValidated)

当你想在MySQL中检索一个OUT变量时#

CREATE TABLE PricingHeaders (
PricingHeaderID bigint NOT NULL AUTO_INCREMENT,
UserID bigint NOT NULL,
ClientName varchar(100) NOT NULL,
Subdivision varchar(100) NOT NULL,
SubjectProperty varchar(100) NOT NULL,
DatePrepared date NOT NULL,
MonthsSearched smallint DEFAULT NULL,
Bedrooms tinyint DEFAULT NULL,
Bathrooms tinyint DEFAULT NULL,
Stories tinyint DEFAULT NULL,
SquareFeet smallint DEFAULT NULL,
YearBuilt smallint DEFAULT NULL,
HasPool bit DEFAULT 0,
Created  datetime DEFAULT CURRENT_TIMESTAMP,
Modified  datetime DEFAULT CURRENT_TIMESTAMP  ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (PricingHeaderID),
UNIQUE KEY UserClientPropertyDate (UserID,ClientName,SubjectProperty,DatePrepared),
INDEX UserSubdivision (UserID,Subdivision)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

CREATE PROCEDURE ExtendSession(IN Token_In VARCHAR(36), OUT tokenValidated int)
BEGIN
SET tokenValidated = 1;
END;

<>以前✓

CREATE PROCEDURE SavePricingHeader ( Token_In VARCHAR(36),
PricingHeaderID_In bigint,
UserID_In bigint,
ClientName_In varchar(100),
Subdivision_In varchar(100),
SubjectProperty_In varchar(100),
DatePrepared_In date,
MonthsSearched_In smallint,
Bedrooms_In tinyint,
Bathrooms_In tinyint,
Stories_In tinyint,
SquareFeet_In smallint,
YearBuilt_In smallint,
HasPool_In bit   )
BEGIN
DECLARE errNo int DEFAULT 0;
DECLARE errMsg varchar(250) DEFAULT 'OK';
DECLARE currOp varchar(250) DEFAULT 'Init';
DECLARE pricingHeaderID1 bigint DEFAULT NULL;
DECLARE pricingHeaderID2 bigint DEFAULT NULL;
DECLARE tokenValidated bit DEFAULT 0;
DECLARE sqlErrNo CHAR(5) DEFAULT '00000';
DECLARE sqlErrMsg TEXT DEFAULT '';

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1 sqlErrNo = RETURNED_SQLSTATE, sqlErrMsg = MESSAGE_TEXT;
SET errNo = 1;
SET errMsg = 'SQL Error';
END;

IF errNo = 0 THEN
SET currOp = 'Extend session';
CALL ExtendSession(Token_In, @tokenValidated);
END IF;

IF errNo = 0 THEN
IF @tokenValidated = 0 THEN
SET errNo = 9001;
SET errMsg = 'Invalid session.';
END IF;
END IF;

IF errNo = 0 THEN
SET currOp = 'Lookup1';
SELECT `PricingHeaderID` INTO pricingHeaderID1 FROM `PricingHeaders` 
WHERE `UserID` = UserID_In
AND `ClientName` = ClientName_In
AND `SubjectProperty` = SubjectProperty_In
AND `DatePrepared` = DatePrepared_In;
IF errNo = 0 THEN
IF pricingHeaderID1 IS NOT NULL THEN
IF pricingHeaderID1 <> PricingHeaderID_In THEN
SET errNo = 9002;
SET errMsg = 'Duplicate Index.';
END IF;
END IF;
END IF;
END IF;

IF errNo = 0 THEN
IF PricingHeaderID_In = 0 THEN
SET currOp = 'Insert';
INSERT INTO `PricingHeaders` (
`UserID`,
`ClientName`,
`Subdivision`,
`SubjectProperty`,
`DatePrepared`,
`MonthsSearched`,
`Bedrooms`,
`Bathrooms`,
`Stories`,
`SquareFeet`,
`YearBuilt`,
`HasPool`
) VALUES (
UserID_In,
ClientName_In,
Subdivision_In,
SubjectProperty_In,
DatePrepared_In,
MonthsSearched_In,
Bedrooms_In,
Bathrooms_In,
Stories_In,
SquareFeet_In,
YearBuilt_In,
HasPool_In
);
IF errNo = 0 THEN
SET pricingHeaderID1 = LAST_INSERT_ID();
END IF;
ELSE        
SET currOp = 'Update';
SET pricingHeaderID1 = PricingHeaderID_In;
UPDATE `PricingHeaders` SET
`ClientName` = ClientName_In,
`Subdivision` = Subdivision_In,
`SubjectProperty` = SubjectProperty_In,
`DatePrepared` = DatePrepared_In,
`MonthsSearched` = MonthsSearched_In,
`Bedrooms` = Bedrooms_In,
`Bathrooms` = Bathrooms_In,
`Stories` = Stories_In,
`SquareFeet` = SquareFeet_In,
`YearBuilt` = YearBuilt_In,
`HasPool` = HasPool_In
WHERE `PricingHeaderID` = PricingHeaderID_In;
END IF;
END IF;
IF errNo = 0 THEN
SET currOp = 'Lookup2';
SELECT `PricingHeaderID` INTO pricingHeaderID2 FROM `PricingHeaders` 
WHERE `UserID` = UserID_In
AND `ClientName` = ClientName_In
AND `SubjectProperty` = SubjectProperty_In
AND `DatePrepared` = DatePrepared_In;
END IF;

IF errNo = 0 THEN
IF pricingHeaderID2 IS NULL THEN
SET errNo = 9003;
SET errMsg = 'Not retrieved.';
END IF;
END IF;

SELECT pricingHeaderID1, pricingHeaderID2, errNo, errMsg, currOp, sqlErrNo, sqlErrMsg;

END

<>以前✓

CALL SavePricingHeader(1,0,1,'test','subd','subepro',NOW(), 1,2,1,1,12,1981,0)

pricingHeaderID1 | pricingHeaderID2 | errNo | errMsg | currOp | sqlErrNo | sqlErrMsg---------------: | ---------------: | ----: | :----- | :------ | :------- | :--------1 | 1 | 0 | OK |查找2 | 00000 |✓

db<此处小提琴>