MySQL数据库加密和解密~认证登陆密码(mysql.user)和MySQL不区分大小写

MySQL数据库中自带old_password(str)和password(str)函数,前者是MySQL323加密,后者是MySQLSHA1方式加密; MYSQL323加密中生成的是16位字符串,而在MySQLSHA1中生存的是41位字符串,其中*是不加入实际的密码运算中,就是说MySQLSHA1加密的密码的实际位数是40位; MYSQL323对应于old_password(str)函数 MYSQLSHA1对应于password(str)函数 注：AES_ENCRYPT()加密函数和AES_DECRYPT()解密函数都需要一个“key”来协助加密。 eg:select aes_encrypt(kaka123,key); select aes_decrypt(aes_encrypt(kaka123,key),key); 1.MYSQL323对应于old_password(str)函数加密：

mysql select old_password(kaka123);

+-------------------------+

| old_password(kaka123) |

+-------------------------+

| 2ee53a20184c9267 |

+-------------------------+

1 row in set, 1 warning (0.00 sec)

2.MYSQLSHA1对应于password(str)函数加密：

 mysql select password(kaka123);

+-------------------------------------------+

| password(kaka123) |

+-------------------------------------------+

| *90B3D884FB6092549F244125549B77C000A0F9C6 |

+-------------------------------------------+

1 row in set (0.00 sec)

3.MYSQL MD5()加密：

mysql select md5(kaka123);

+----------------------------------+

| md5(kaka123) |

+----------------------------------+

| 5d052f1e32af4e4ac2544a5fc2a9b992 |

+----------------------------------+

1 row in set (0.00 sec)

4.AES_ENCRYPT()加密函数

mysql select aes_encrypt(kaka123,key);

+------------------------------+

| aes_encrypt(kaka123,key) |

+------------------------------+

| D#jA

+------------------------------+

1 row in set (0.00 sec)

5.AES_DECRYPT()解密函数

mysql select aes_decrypt(aes_encrypt(kaka123,key),key);

+-------------------------------------------------+

| aes_decrypt(aes_encrypt(kaka123,key),key) |

+-------------------------------------------------+

| kaka123 |

+-------------------------------------------------+

1 row in set (0.00 sec)

4.AES_ENCRYPT()加密函数

mysql select host,user,password from mysql.user;

+----------------+------+-------------------------------------------+

| host | user | password |

+----------------+------+-------------------------------------------+

| localhost | root | *71ABCA8B06D46066CEF8062A75256E66243D0FC8 |

| tony\_ts\_tian | root | *71ABCA8B06D46066CEF8062A75256E66243D0FC8 |

| 127.0.0.1 | root | *71ABCA8B06D46066CEF8062A75256E66243D0FC8 |

| ::1 | root | *71ABCA8B06D46066CEF8062A75256E66243D0FC8 |

| % | root | *71ABCA8B06D46066CEF8062A75256E66243D0FC8 |

| % | kaka | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

+----------------+------+-------------------------------------------+

6 rows in set (0.00 sec)

mysql update mysql.user set password=AES_ENCRYPT("kaka123",key) where host=% and user=kaka;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql select host,user,password from mysql.user;

+----------------+------+-------------------------------------------+

| host | user | password |

+----------------+------+-------------------------------------------+

| localhost | root | *71ABCA8B06D46066CEF8062A75256E66243D0FC8 |

| tony\_ts\_tian | root | *71ABCA8B06D46066CEF8062A75256E66243D0FC8 |

| 127.0.0.1 | root | *71ABCA8B06D46066CEF8062A75256E66243D0FC8 |

| ::1 | root | *71ABCA8B06D46066CEF8062A75256E66243D0FC8 |

| % | root | *71ABCA8B06D46066CEF8062A75256E66243D0FC8 |

| % | kaka | œDŸ#jAÂ

 | ¯Ll 

+----------------+------+-------------------------------------------+

6 rows in set (0.00 sec)

5.AES_DECRYPT()解密函数

mysql select host,user,AES_DECRYPT(password,key) from mysql.user;

+----------------+------+-----------------------------+

| host | user | AES_DECRYPT(password,key) |

+----------------+------+-----------------------------+

| localhost | root | NULL |

| tony\_ts\_tian | root | NULL |

| 127.0.0.1 | root | NULL |

| ::1 | root | NULL |

| % | root | NULL |

| % | kaka | kaka123 |

+----------------+------+-----------------------------+

6 rows in set (0.00 sec)

mysql不区分大小写:

修改MySql Server安装目录下的 my.ini 或my.conf文件

在mysqld节下加入下面一行

lower_case_table_names=0 

（0：大小写敏感；1：大小写不敏感）最后重启一下MySql服务即可。 

修改对于新建的表，老表无效

#解决mysql字段不区分大小写:

CREATE TABLE `user`(

 `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT用户登录表主键id,

 `uname` VARCHAR(35) NOT NULL COMMENT用户登录名,

 `upass` VARCHAR(32) NOT NULL COMMENT用户登录密码,

 `ustatus` VARCHAR(2) NOT NULL DEFAULT 0 COMMENT用户登录状态,0为正常，1为锁定,

 UNIQUE (`uname`),

 PRIMARY KEY(`id`)

)ENGINE = INNODB COLLATE=utf8_bin COMMENT=用户登录表;

注：COLLATE=utf8_bin