MySQL通过password()选择不返回预期结果 [英] MySQL select by password() does not return expected result
问题描述
在MySQL中,我执行
In MySQL I execute
insert into test (id, pw) values(1, password('1234'));
我知道了
Query OK, 1 row affected, 1 warning (0.01 sec)
我想通过密码搜索记录,所以我执行
I want to search a record by password, so I execute
select * from test where pw = password('1234');
我希望它返回一行,但是我得到了一个空集.我做错了什么?
I expect it to return one row, but I get an empty set. What did I do wrong?
推荐答案
MySQL Password()
函数当前生成一个非二进制字符串(大约2016年),最多41个字符.通过调用
The MySQL Password()
function generates a nonbinary string currently (circa 2016) up to 41 characters. This is visible thru either calls to
SHOW CREATE TABLE mysql.user;
并检查password
列(其中包含哈希值):
and examining the password
column (which holds a hashed value):
`Password` char(41) ...
例如,对于MySQL 5.6,或通过对相同的Cleartext值进行哈希处理并将其排列到MySQL 5.7上相同的SHOW CREATE TABLE
的输出中
for, say, MySQL 5.6, or by hashing the same Cleartext value and lining it up to the output of the same SHOW CREATE TABLE
on MySQL 5.7
`authentication_string` text ...
哈希值是一致的,但是处于不同的架构布局中.同样,一个在VARCHAR(41)
中,另一个在TEXT
中,与
The hash values are consistent, yet in a different schema layout. Again, one in a VARCHAR(41)
, the other in a TEXT
, as the same mysql_native_password
PAM is being used. For now. Password()
became deprecated as of 5.7.6 which means a new Plugin is in the works. Which they should be. They are plugins afterall.
这是什么意思?这意味着您的架构需要具有足够宽的列以处理对Password()
的使用(请注意5.7的TEXT切换).并且请记住,它已被弃用,因此请注意未来几年MySQL哈希的更改.
What does it all mean? It means your schema needs to have a wide enough column to handle your use of Password()
(note 5.7's switch to TEXT). And remember it is deprecated so keep an ear out for changes with MySQL hashing in the next few years.
这篇关于MySQL通过password()选择不返回预期结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!