How to hash passwords in MySQL?

问题:

How I will make every password in my user table encrypted(md5()) except one particular row using a single query?

回答1:

UPDATE table SET Password = MD5(Password)

I will say though that MD5 isn't a very good level of encryption and you should consider something stronger such as ENCRYPT with a custom salt. Read about it here

EDIT: Looks like the original question changed. Here's the altered query to accomodate

UPDATE table SET Password = MD5(Password) WHERE ID!=[specified index]

EDIT: Worth noting

MD5 Encryption Hacked

回答2:

Hash Functions in MySQL

There are a lot more hash functions than MD5 to use for storing passwords in you MySQL database.
You can find a list of them on MySQL :: 11.10.2. Encryption and Compression Functions.

Save Password (hash):

UPDATE users SET password = SHA('secret_password') WHERE ....;

Check Password:

SELECT COUNT(*) FROM users WHERE name = 'username' && password = SHA('typed_password');

If the result is > 0, the user provided the correct password.

回答3:

Concerning you edit: do you have an ID or username that identifies this row?

UPDATE mytable
SET password = MD5(password)
WHERE id <> 123

回答4:

Edited in response to edit in OP.

UPDATE userTable
SET password = MD5(password)
WHERE NOT (<criteria to identify row to exclude>)

回答5:

When hashing passwords, do not forget to salt them, so that same passwords do not yield same hashes:

SET @salt := CONV(FLOOR(RAND() * 0x100000000), 10, 16)

UPDATE  passwords
SET     password = CONCAT(@salt, SHA(CONCAT(@salt, @typed_password)))

SELECT  1
FROM    passwords
WHERE   SHA(CONCAT(SUBSTRING(password, 1, 8), @typed_password)) = SUBSTRING(password, 9, 40)

回答6:

I think it is a little bit more update

SET PASSWORD FOR 'existinguser'@'localhost' = PASSWORD('newpass');

or

UPDATE user SET  password = PASSWORD('newpass');

Hope this help