Modify and copy fields in a MySQL DATA table in batches

The following articles mainly describe the batch modification and replication of fields in the MySQL DATA table. Recently, some troubles have occurred. When the newly installed official Community of PHPwind 6.0 imports the member account information before, we found that the mail addresses of many Members seem to be randomly filled out. In PHPwind5.5, the mail address verification function was not enabled,

Therefore, it is estimated that many users fill in the mail address randomly, so I want to ask all formal members to re-verify the email address to re-activate the member account. The result shows that the Community does not have this function at all, after a long struggle, the MySQL DATA table of the database member information was repeatedly installed = deleted several times, and finally found a method for batch modification.

However, this operation will even change the account of the Community creator to inactive, so if you are not clear about it, do not do it easily. Note that you need to back up the relevant data before the operation.

In the PHPwind 6.0 database, its membership information is stored in pw_members, the value displayed in the "yz" Field of the MySQL DATA table under the normal state of the member account is 1, indicating that the account has been activated ). If you want to activate the email again, the content displayed for this value must be the same as the value of another field "regdate, during verification, the program automatically checks whether the content of the two fields is exactly the same before performing the verification. Otherwise, a parameter error message is displayed.

For example, if the value of the "regdate" field in UID 1 is 1194067635, the value of the "yz" field is 1194067635. Similarly, if the value of the "regdate" field in UID 2 is 1175320740, the value of the "yz" field is also 1175320740...

In this case, for various possible reasons, the value of the "yz" field may not be 1, indicating that the account has been activated, how can we make the value of the "yz" field consistent with that of the "regdate" field? At the same time, because there are more than N member information, how can we batch convert the value of this field for all member accounts? I used this method.

Updated table SET replaced field name = copied field name

Apply to this example: UPDATE pw_members SET yz = regdate

"Pw_members" indicates the name of the data table. "replaced field name" indicates the name of the field to be modified in "pw_members". Here "yz" is used "; the "Copied field name" refers to the field name that is provided to the "yz" field for copying !) "Regdate"; in this way, the values of "yz" in each UID can be modified in batches and copied to the same value as "regdate. The member will ask the Member to activate the email account again upon the next login.

This is a batch modification, because all accounts, including the Community creators, will be changed to inactive, so after the execution is complete, immediately change the account that does not need to be activated, including the Community creator, back to normal. The method is to browse the pw_members data table, select the member account you want to change back to the normal state, click "edit" to enter and find the "yz" field, change the field value (for example, "1194067635") to "1", so that the member account will return to normal; you can also use an administrator account to manually activate the required member ID in "Member Management" in the community management background.

In addition, for how to manually convert the content of different fields in the same MySQL Data Table in batches, refer to the following command:

UPDATE table set field name = REPLACE (field name, 'original string', 'replacement string') where known field name LIKE '% original string %'

Application to this example:

 
 

  
  
UPDATE pw_members set yz=REPLACE(yz,'1','2') where yz LIKE '%1%' 
 
 

"Pw_members" indicates the name of the MySQL DATA table. "field name" indicates the name of the field to be modified in "pw_members". Here "yz" is used "; the "original string" is the current default value of "1" for the field "yz "; the "replaced string" refers to the new default value of "2" for the field "yz" to be modified ".