我试图通过标识存储为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))

10-04 21:57