How to search for and replace a text string in MariaDB on Linux

There will come a time when you must search for a particular text string in a field in MariaDB, and you may not know what database or table it might be in. It is somewhat like searching for a needle in a haystack, but fortunately we have good tools for finding our needle. I like to dump the database I’m searching into a text file and do my searches on the file, because it’s a fast way to search, and you can mangle the dump file all you want without damaging anything important. When you have the results, you can run SQL queries on the appropriate tables to make the replacements.

As always, be sure you have good backups before you muck around with your MariaDB server. Even better, have a copy of your production server in a sandbox for doing test runs.

First, export your database into a text file, then search the file with your usual text-processing commands (such asgrep), or use the search function in your text editor. This command exports the whole works into a single text file:

$ mysqldump -u user -p --extended-insert=false --all-databases  > dbdump.txt

Or you can limit your dump to a specific set of databases:

$ mysqldump -u user -p --extended-insert=false --databases db1 db2 db3 > dbdump.txt

You must use the --extended-insert=false option to format the dump file with line breaks. If you don’t, you’ll get a mass of data without line breaks, which would be difficult to read. It would also defeat grep; when grep finds a match for your search term, it prints the whole line. No line breaks = big indigestible blob.

This example uses grep to perform a case-insensitive search in a film database for the name Truman:

$ grep -i truman  dbdump.txt 

INSERT INTO `film` VALUES (6,'AGENT TRUMAN');
INSERT INTO `film` VALUES (537,'LOVER TRUMAN');
INSERT INTO `film` VALUES (579,'MINDS TRUMAN');
INSERT INTO `film` VALUES (613,'MYSTIC TRUMAN');
INSERT INTO `film` VALUES (915,'TRUMAN CRAZY');
INSERT INTO `film_text` VALUES (6,'AGENT TRUMAN');
INSERT INTO `film_text` VALUES (537,'LOVER TRUMAN');
INSERT INTO `film_text` VALUES (579,'MINDS TRUMAN');
INSERT INTO `film_text` VALUES (613,'MYSTIC TRUMAN');
INSERT INTO `film_text` VALUES (915,'TRUMAN CRAZY');

And there you go – 10 results, showing which tables they’re in: namely “film” and “film_text.”

If you dumped multiple databases, how do you know which ones the tables you found belong to? Log into your mysqlshell and query information_schema to list all databases and their tables. This example shows how to exclude MariaDB’s internal tables:

$ mysql -u root -p
MariaDB [(none)]> SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema NOT IN ( 'information_schema', 'performance_schema', 'mysql');
+--------------+------------+
| table_schema | table_name |
+--------------+------------+
| db1          | table1     |
| videos       | film       |   
| videos       | film_text  |
+--------------+------------+

You also need to know the field name that your text string is in, which you can also find with mysql now that you know which database to use:

MariaDB [(none)]> USE videos;
MariaDB [videos]> SHOW COLUMNS FROM film;
+-----------+-------------------------+------+-----+---------+
| Field     | Type                    | Null | Key | Default |
+---------- +-------------------------+------+-----+---------+
| film_id   | smallint(5) unsigned    | NO   | PRI | NULL    |
| title     | varchar(255)            | NO   | MUL | NULL    | 
+-----------+-------------------------+------+-----+---------+

In our search results, fields appear in order between the parentheses, and our search term is the second item, which puts it in the “title” field. Now you can use a SQL query to find “Truman” and replace it with another string – “Wilson,” let’s say. Mind your cases on your seach and replace terms:

MariaDB [videos]> UPDATE film SET title = REPLACE(title, "TRUMAN", "WILSON");
Query OK, 5 rows affected (0.03 sec)
Rows matched: 1000  Changed: 5  Warnings: 0

Now check your work:

MariaDB [videos]> SELECT title FROM film WHERE title LIKE '%wilson%';

+---------------+
| title         |
+---------------+
| AGENT WILSON  |
| LOVER WILSON  |
| MINDS WILSON  |
| MYSTIC WILSON |
| WILSON CRAZY  |
+---------------+

Well allrighty then, it worked.

What if you want to replace just a subset of your search results, like the first two titles? Adjust your REPLACE syntax to find and replace a match for the whole title:

MariaDB

> UPDATE film SET title = REPLACE(title, "AGENT TRUMAN", "AGENT WILSON");

To learn more about fine-tuning your search and replace expressions, consult the REPLACE syntax page and Regular Expressions Overview.