我试图通过标识存储为SMALLINT的userID和存储为BINARY(32)的passwordTest来从表中选择所有数据。这是我用来制作表格的查询:
{ {CREATE TABLE IF NOT EXISTS `test`.`User`
(`UserID` SMALLINT UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
`firstname` VARCHAR(35) NOT NULL,
`lastname` VARCHAR(35) NOT NULL,
`passwordTest` BINARY(32) NOT NULL,
`registration_date` DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`UserID`),
UNIQUE INDEX `password_UNIQUE` (`passwordTest` ASC));}
这是我用来将测试值插入表中的查询:
{INSERT INTO USER (firstname, lastname, passwordTest)
VALUES('Test','1','password');}
这是我无法执行的SQL查询:
{USE test;
SELECT * FROM USER WHERE userID = '00001' AND passwordTest = 'password';}
任何帮助将非常感激
最佳答案
因为您将密码列定义为二进制,所以必须在where子句中将密码转换为二进制。这在mysql5.6中有效Select * From User Where UserID = '00001' and passwordTest = cast('password' as BINARY(32))