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屋！