Mysql – Regex replace with empty string results in NULL characters in between

MySQLmysql-8.0regex

Replacing a character with regexp_replace results in NUL characters in between.

SELECT REGEXP_REPLACE(aColumn,'c', '')
INTO OUTFILE 'Replaced.csv';

for abcd results in:

ab d (infact: abNULd)

This does not occur with just REPLACE

using MySQL 8

Best Answer

Ugh! The plot thickens:

mysql> SELECT REGEXP_REPLACE('abcd', 'c', '') = 'abd';
+-----------------------------------------+
| REGEXP_REPLACE('abcd', 'c', '') = 'abd' |
+-----------------------------------------+
|                                       1 |
+-----------------------------------------+

mysql> SELECT HEX(REGEXP_REPLACE('abcd', 'c', '')), HEX('abd');
+--------------------------------------+------------+
| HEX(REGEXP_REPLACE('abcd', 'c', '')) | HEX('abd') |
+--------------------------------------+------------+
| 610062006400                         | 616264     |
+--------------------------------------+------------+

mysql> SELECT LENGTH(REGEXP_REPLACE('abcd', 'c', '')), LENGTH('abd');
+-----------------------------------------+---------------+
| LENGTH(REGEXP_REPLACE('abcd', 'c', '')) | LENGTH('abd') |
+-----------------------------------------+---------------+
|                                       6 |             3 |
+-----------------------------------------+---------------+

mysql> SELECT @@version;
+-----------+
| @@version |
+-----------+
| 8.0.15    |
+-----------+

Looks like there are NULs all over! And looks like they are ignored--somewhat!

I filed http://bugs.mysql.com/94203

MariaDB 10.3.11 "does the right thing" ( 1 / no NULs / 3,3 )

Related Solutions

MySQL – How to Copy a Table Between Servers

I tend to shy away from the FEDERATED storage engine for three(3) reasons:

It only supports MyISAM
DDL performed on the source table requires manually changing the FEDERATED table design on external services.
Bulk operations against a FEDERATED table can become an instant mightmare !!!

What to do ???

One possibility is to use mysqldump and merge them

EXAMPLE
Assuming all tables have identical structures
Assuming all tables are not using auto_increment IDs
Assuming all tables are InnoDB

On the server that will combine the three tables, create four(4) import databases (one time operation):

CREATE DATABASE ImportDB1;
CREATE DATABASE ImportDB2;
CREATE DATABASE ImportDB3;
CREATE DATABASE ImportCombined;

Import Process Goes Something Like This

mysqldump the three(3) tables

mysqldump -hHOSTIP1 -Pportnumber1 -u... -p... dbname tbname > /root/ImportTable1.sql &
mysqldump -hHOSTIP2 -Pportnumber2 -u... -p... dbname tbname > /root/ImportTable2.sql &
mysqldump -hHOSTIP3 -Pportnumber3 -u... -p... dbname tbname > /root/ImportTable3.sql &
wait

Load the mysqldumps into the separate import databases in parallel:

mysql -hHOSTIP4 -u... -p... -A -DImportDB1 < /root/ImportTable1.sql &
mysql -hHOSTIP4 -u... -p... -A -DImportDB2 < /root/ImportTable2.sql &
mysql -hHOSTIP4 -u... -p... -A -DImportDB3 < /root/ImportTable3.sql &
wait
rm -f /root/ImportTable1.sql &
rm -f /root/ImportTable2.sql &
rm -f /root/ImportTable3.sql &
wait

Next, combine the data.

DROP TABLE IF EXISTS ImportCombined.tbname;
CREATE TABLE ImportCombined.tbname LIKE ImportDB1.tbname;
ALTER TABLE ImportCombined.tbname DISABLE KEYS;
INSERT INTO ImportCombined.tbname SELECT * FROM ImportDB1.tbname;
INSERT INTO ImportCombined.tbname SELECT * FROM ImportDB2.tbname;
INSERT INTO ImportCombined.tbname SELECT * FROM ImportDB3.tbname;
ALTER TABLE ImportCombined.tbname ENABLE KEYS;
ALTER TABLE ImportCombined.tbname ENABLE KEYS;

Now, perform the task you already have in place against the ImportCombined.tbname table.

"You fill in these steps"

Then, when the table is ready to be exported, mysqldump it and copy 2 times:

mysqldump -hHOSTIP4 -Pportnumber4 -u... -p... ImportCombined tbname > /root/ExportTable1.sql
cp /root/ExportTable1.sql /root/ExportTable2.sql &
cp /root/ExportTable1.sql /root/ExportTable3.sql &
mysql -hHOSTIP1 -Pportnumber1 -u... -p... -A -Ddbname < /root/ExportTable1.sql &
mysql -hHOSTIP2 -Pportnumber2 -u... -p... -A -Ddbname < /root/ExportTable2.sql &
mysql -hHOSTIP3 -Pportnumber3 -u... -p... -A -Ddbname < /root/ExportTable3.sql &
wait
rm -f /root/ExportTable[123].sql &

As show from these steps, these things should be shell scripted and perhaps crontab'd.

Here is another maneuver: If the tables are MyISAM, you could copy the MyISAM tables (.frm, .MYD, and .MYI files for each table) in other folders (databases in the eyes of mysqld) instead of doing mysqldumps, and perform similar operations as mentioned before. Make sure no writes to the tables take place during the copy.

MySQL Replace — Query or in Shell

You could try to query the table information_schema.columns and find every table in your MySQL DB Instance that have character fields. Here is the table's layout:

mysql> show create table information_schema.columns\G
*************************** 1. row ***************************
       Table: COLUMNS
Create Table: CREATE TEMPORARY TABLE `COLUMNS` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `COLUMN_NAME` varchar(64) NOT NULL DEFAULT '',
  `ORDINAL_POSITION` bigint(21) unsigned NOT NULL DEFAULT '0',
  `COLUMN_DEFAULT` longtext,
  `IS_NULLABLE` varchar(3) NOT NULL DEFAULT '',
  `DATA_TYPE` varchar(64) NOT NULL DEFAULT '',
  `CHARACTER_MAXIMUM_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `CHARACTER_OCTET_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `NUMERIC_PRECISION` bigint(21) unsigned DEFAULT NULL,
  `NUMERIC_SCALE` bigint(21) unsigned DEFAULT NULL,
  `CHARACTER_SET_NAME` varchar(32) DEFAULT NULL,
  `COLLATION_NAME` varchar(32) DEFAULT NULL,
  `COLUMN_TYPE` longtext NOT NULL,
  `COLUMN_KEY` varchar(3) NOT NULL DEFAULT '',
  `EXTRA` varchar(27) NOT NULL DEFAULT '',
  `PRIVILEGES` varchar(80) NOT NULL DEFAULT '',
  `COLUMN_COMMENT` varchar(1024) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

Here is a query to get every character-based column type for all user-defined tables:

mysql> select DISTINCT COLUMN_TYPE from information_schema.columns
    -> WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
    -> AND (COLUMN_TYPE REGEXP 'text$'
    -> OR COLUMN_TYPE REGEXP '^varchar'
    -> OR COLUMN_TYPE REGEXP '^char');
+--------------+
| COLUMN_TYPE  |
+--------------+
| mediumtext   |
| varchar(512) |
| varchar(20)  |
| longtext     |
| text         |
| varchar(200) |
| varchar(255) |
| varchar(100) |
| tinytext     |
| varchar(25)  |
| varchar(64)  |
| varchar(32)  |
| varchar(60)  |
| varchar(50)  |
| varchar(250) |
| varchar(150) |
| varchar(10)  |
| varchar(45)  |
| char(5)      |
+--------------+
19 rows in set (0.09 sec)

You could join that query to a second query. The second query would have the name of every table with those column types:

SELECT BB.table_schema,BB.table_name,BB.COLUMN_NAME FROM
(select DISTINCT COLUMN_TYPE from information_schema.columns
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
AND (COLUMN_TYPE REGEXP 'text$'
OR COLUMN_TYPE REGEXP '^varchar'
OR COLUMN_TYPE REGEXP '^char')) AA
INNER JOIN
(SELECT table_schema,table_name,COLUMN_NAME,COLUMN_TYPE
FROM information_schema.columns
WHERE table_schema NOT IN
('mysql','information_schema','performance_schema')) BB
USING (COLUMN_TYPE);

Now, message the output to make it write a generic script for you:

SELECT CONCAT('UPDATE ',db,'.',tb,' SET ',fld,
'=REPLACE(',fld,',''oldchar'',''newchar'');')
UpdateCommand
FROM (SELECT db,tb,fld FROM
(
    SELECT table_schema db,table_name tb,COLUMN_NAME fld FROM
    (select DISTINCT COLUMN_TYPE from information_schema.columns
    WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
    AND (COLUMN_TYPE REGEXP 'text$'
    OR COLUMN_TYPE REGEXP '^varchar'
    OR COLUMN_TYPE REGEXP '^char')) AAA
    INNER JOIN
    (SELECT table_schema,table_name,COLUMN_NAME,COLUMN_TYPE
    FROM information_schema.columns
    WHERE table_schema NOT IN
    ('mysql','information_schema','performance_schema')) BBB
    USING (COLUMN_TYPE)
) AA) A;

Output should look something like this:

+-----------------------------------------------------------------------------------------------+
| UpdateCommand                                                                                 |
+-----------------------------------------------------------------------------------------------+
| UPDATE example.user SET user_name=REPLACE(user_name,'oldchar','newchar');                     |
| UPDATE garbage.rolando SET post_content=REPLACE(post_content,'oldchar','newchar');            |
| UPDATE garbage.rolando SET post_title=REPLACE(post_title,'oldchar','newchar');                |
| UPDATE garbage.rolando SET post_excerpt=REPLACE(post_excerpt,'oldchar','newchar');            |
| UPDATE garbage.rolando SET post_status=REPLACE(post_status,'oldchar','newchar');              |
| UPDATE garbage.rolando SET comment_status=REPLACE(comment_status,'oldchar','newchar');        |
| UPDATE garbage.rolando SET ping_status=REPLACE(ping_status,'oldchar','newchar');              |
| UPDATE garbage.rolando SET post_password=REPLACE(post_password,'oldchar','newchar');          |
| UPDATE garbage.rolando SET post_name=REPLACE(post_name,'oldchar','newchar');                  |

Last step for generic script generation is to output the query to a text file like this:

mysql -u... -p... -A --skip-column-names -e"SELECT CONCAT('UPDATE ',db,'.',tb,' SET ',fld, '=REPLACE(',fld,',''oldchar'',''newchar'');') UpdateCommand FROM (SELECT db,tb,fld FROM (     SELECT table_schema db,table_name tb,COLUMN_NAME fld FROM     (select DISTINCT COLUMN_TYPE from information_schema.columns     WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')     AND (COLUMN_TYPE REGEXP 'text$'     OR COLUMN_TYPE REGEXP '^varchar'     OR COLUMN_TYPE REGEXP '^char')) AAA     INNER JOIN     (SELECT table_schema,table_name,COLUMN_NAME,COLUMN_TYPE     FROM information_schema.columns     WHERE table_schema NOT IN     ('mysql','information_schema','performance_schema')) BBB USING (COLUMN_TYPE) ) AA) A;" > CharUpdateScript.sql

You can customize the generic script from there.

Best Answer

Related Solutions

MySQL – How to Copy a Table Between Servers

MySQL Replace — Query or in Shell

Related Question