MySQL 中的单向加密函数

冷不防 2022-12-10 01:19 139阅读 0赞

### PASSWORD() ###

PASSWORD() 返回加密的密码，此函数已经被废弃并在MySQL8.0之后被移除。以下是笔者在不同版本mysql服务器测试的结果：

mysql> select version();
    +-----------+
    | version() |
    +-----------+
    | 5.7.26    |
    +-----------+
    1 row in set (0.03 sec)
    
    mysql> select password('122');
    +-------------------------------------------+
    | password('122')                           |
    +-------------------------------------------+
    | *652552781BA557B50DDDA5C3633D49FBC5A50081 |
    +-------------------------------------------+
    1 row in set, 1 warning (0.00 sec)
    
    mysql> show warnings;
    +---------+------+-------------------------------------------------------------------+
    | Level   | Code | Message                                                           |
    +---------+------+-------------------------------------------------------------------+
    | Warning | 1681 | 'PASSWORD' is deprecated and will be removed in a future release. |
    +---------+------+-------------------------------------------------------------------+
    1 row in set (0.00 sec)

mysql> select version();
    +-----------+
    | version() |
    +-----------+
    | 8.0.15    |
    +-----------+
    1 row in set (0.03 sec)
    
    mysql> select password('122');
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('122')' at line 1

### MD5() ###

mysql> select md5('123456'),md5(123456)\G
    *************************** 1. row ***************************
    md5('123456'): e10adc3949ba59abbe56e057f20f883e
      md5(123456): e10adc3949ba59abbe56e057f20f883e
    1 row in set (0.04 sec)

MD5函数会将其中的数据始终按照字符来处理，而且，在参数是表达式的情况下会先执行表达式，然后将表达式结果加密：

mysql> select md5(123450+6);
    +----------------------------------+
    | md5(123450+6)                    |
    +----------------------------------+
    | e10adc3949ba59abbe56e057f20f883e |
    +----------------------------------+
    1 row in set (0.03 sec)

### SHA 加密 ###

SHA等同于SHA1加密。

mysql> select SHA('123'),SHA1('123') \G
    *************************** 1. row ***************************
     SHA('123'): 40bd001563085fc35165329ea1ff5c5ecbdbbeef
    SHA1('123'): 40bd001563085fc35165329ea1ff5c5ecbdbbeef
    1 row in set (0.03 sec)

### SHA2加密 ###

SHA2(str, hash\_length)  
hash\_length支持的值224, 256, 384, 512, or 0。0等同于256。

mysql> select SHA2('123456', 0),SHA2('123456',256) \G
    *************************** 1. row ***************************
     SHA2('123456', 0): 8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92
    SHA2('123456',256): 8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92
    1 row in set (0.03 sec)
    
    mysql> select SHA2('123456', 224),SHA2('123456',256),SHA2('123456',384),SHA2('123456',512)\G
    *************************** 1. row ***************************
    SHA2('123456', 224): f8cdb04495ded47615258f9dc6a3f4707fd2405434fefc3cbf4ef4e6
     SHA2('123456',256): 8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92
     SHA2('123456',384): 0a989ebc4a77b56a6e2bb7b19d995d185ce44090c13e2984b7ecc6d446d4b61ea9991b76a4c2f04b1b4d244841449454
     SHA2('123456',512): ba3253876aed6bc22d4a6ff53d8406c6ad864195ed144ab5c87621b6c233b548baeae6956df346ec8c17f5ea10f35ee3cbc514797ed7ddd3145464e2a0bab413
    1 row in set (0.04 sec)