In MySQL documentation for PASSWORD function:
The PASSWORD() function is used by the authentication system in MySQL Server; you should not use it in your own applications. For that purpose, consider MD5() or SHA1() instead.
Why we shouldn't use this function in our application?
A few reasons I can think of
It's a fast hash (SHA1 I believe) which isn't a good property for password hashes.
They might change what hash it uses in a future version of MySQL, breaking your application. They've already done this once, hence the OLD_PASSWORD() function.
It doesn't naturally use a salt (although you could use a salt with it if you wanted to by appending it to the password before calling the PASSWORD function)
It's non-standard SQL, so if you ever need to port your app to another platform you'll need to come up with a replacement
