mysql - 如何通过一个简单的sql语句实现十万条数据更新？

Question

商城系统 导入用户数据（user表 id username password pay_password） 大约10万条，由于密码是明文的，需要使用我们商城自己的加密方式（php方法加密），加密之后再更新 用户表数据。

笨方法 就是把十万条数据读出来，然后循环更新user表。

求高手指点一下，还有没有别的方式实现？

Answer 1

I don’t know much about sql programming. If your encryption algorithm can be written as a sql function, just execute update directly.

update user set password = encryption(password)

Answer 2

I don’t know the specific purpose of the password here, and I don’t know whether the questioner has strict requirements for the password plaintext encryption algorithm.
If there is no requirement and the password is only used for matching, there is a convenient password function in mysql to encrypt the plaintext password into ciphertext. It may be a simple way to use the update statement to execute this function on all password fields.

Answer 3

Create a new table and write insertAll, then replace the old table with the new table.

Answer 4

I suggest adding a new field to the old table to store the encrypted password. After the test is successful, delete the plain text password field. Anyway, the Kendi code needs to be rewritten where the plain text password field is used. For the new table, if it is an auto-incremented primary key, It might be different.

Answer 5

Disposable things, how to make them as simple as possible