MySQL UPDATE statement, modify fields, or bulk modify fields

update A field, after it adds a string, do not query the database to get this field value how to add?? For example: My test table has a user field, and I now want to add the name of another username in the MySQL database so I write update test SET user', Phpchina'   '2'; This is wrong, the MySQL database adds it as a number, and the user field value becomes 0. Should: UPDATE test SET user= CONCAT (user,', Phpchina')  '2  ';

MySQL Bulk replace specified field string statement

UPDATE data table name set field name = Replace (field name, ' String to replace ', ' replace with ') WHERE set condition;

Example

The code is as follows	Copy Code
UPDATE ' cdb_name ' SET ' field_name ' = replace (' Field_name ', ' from_str ', ' to_str ')

Description

cdb_name--the name of the table in which the character or string resides
field_name--the field name of the field in which the character or string resides
from_str--the string to replace
to_str--replaced by string

For example:

Go to phpMyAdmin, open the database you want to manipulate, click SQL above, execute the following SQL statement: (here I use the Discuz to do the demo)

The code is as follows	Copy Code
UPDATE ' pre_forum_post ' SET ' author ' = replace (' author ', ' Xiang Lei tribe ', ' Xiang Lei ')

It means to change the user name "Xiang Lei tribe" to "Xiang Lei", where pre_ to modify the table prefix for your own database.

The code is as follows	Copy Code
UPDATE phpcms_article SET title=replace (title, ' [2009] ', ' (2009) '); UPDATE phpcms_article SET content=replace (Content, ' [2009] ', ' (2009) ');

You can also include substitution criteria in the statement, such as replacing only content with a qualified ID of less than 200:

The code is as follows	Copy Code
UPDATE wp_posts SET post_content = replace (post_content, ' Search engine optimization ', ' Search engine marketing ') WHERE ID < 200;

With an advanced point in the back.

The code is as follows	Copy Code
UPDATE ' cdb_settings ' SET ' value ' = ' WHERE CONVERT ' (' variable ' USING utf8) = ' adminipaccess ' LIMIT 1;

MySQL UPDATE statement, modify fields, or bulk modify fields