MySQL數據庫認證密碼有兩種方式:

1：MySQL 4.1版本之前是MySQL323加密

2：MySQL 4.1和之后的版本都是MySQLSHA1加密

還有函數:AES_ENCRYPT()加密函數和AES_DECRYPT()解密函數和MD5()加密。

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