[mysql] MySQL 쿼리를 사용하여 전체 테이블에서 텍스트 찾기 및 바꾸기

이것을 PHP 파일에 넣고 실행하면 원하는 작업을 수행해야합니다.

// Connect to your MySQL database.
$hostname = "localhost";
$username = "db_username";
$password = "db_password";
$database = "db_name";

mysql_connect($hostname, $username, $password);

// The find and replace strings.
$find = "find_this_text";
$replace = "replace_with_this_text";

$loop = mysql_query("
    SELECT
        concat('UPDATE ',table_schema,'.',table_name, ' SET ',column_name, '=replace(',column_name,', ''{$find}'', ''{$replace}'');') AS s
    FROM
        information_schema.columns
    WHERE
        table_schema = '{$database}'")
or die ('Cant loop through dbfields: ' . mysql_error());

while ($query = mysql_fetch_assoc($loop))
{
        mysql_query($query['s']);
}

답변

UPDATE `tj_posts`
SET `post_content` = replace(post_content, 'mysite.com/wordpress', 'mysite.com/news')

SELECT CONCAT(
    'update ', table_name , 
    ' set ', column_name, ' = replace(', column_name,', ''www.oldDomain.com'', ''www.newDomain.com'');'
) AS statement
FROM information_schema.columns
WHERE table_schema = 'mySchema' AND table_name LIKE 'yourPrefix_%';

 UPDATE table SET field = replace(field, text_needs_to_be_replaced, text_required);

UPDATE student SET student_name = replace(student_name, 'John', 'Mark');