mysql - How does php binary encrypted data ensure data uniqueness?

The sensitive user data in the company database needs to be encrypted into binary ciphertext and stored in the mysql blob type field. Now it is found that it is difficult to ensure the uniqueness of the user data, because it seems that the blob type field cannot be directly subjected to logical operations, nor can the table Read out all the ciphertext, decrypt it and then perform logical operations

Reply content:

The sensitive user data in the company database needs to be encrypted into binary ciphertext and stored in the mysql blob type field. Now it is found that it is difficult to ensure the uniqueness of the user data, because it seems that the blob type field cannot be directly used for logical operations, nor can the table Read out all the ciphertext, decrypt it and then perform logical operations

Cache ciphertext md5 based on user id

I think this should be done:

1. When adding new data, perform md5 hashing of the plain text before encryption, and then save the hashed value (of course the encrypted blob must also be saved)

2. As for the existing data, you need to find time to decrypt all md5, and then store the hashed value

To determine the uniqueness in this way, it is usually enough to directly determine the hashed value.

It is not recommended to md5 ciphertext -- unless you can ensure that the same plaintext encryption will always produce the same ciphertext. However, the ciphertext will at least be affected by the key. After changing the key, the ciphertext will definitely change... Moreover, some encryption methods themselves have randomized initial vectors, and the ciphertext encrypted is different each time.

Direct mysql built-in method hex()