How to add a string with a literal backslash in mysql, using ruby/rails migrations?

Question

I'm trying to update a mariadb table column, to a string that contains a literal backslash.

I want the resulting string in the table to be

4.4 \(blah blah\)

I've tried

UPDATE table SET string = '4.4 \\(blah blah\\)' WHERE string = '4.4 (blah blah)';

This works when I run it in Sequel Pro, but when I run it as part of a ruby/rails migration, the result is that the column remains unchanged, ie. 4.4 (blah blah).

I've tried every combination of single quotes, double quotes, single backslash, double backslash. I also tried a triple backslash.

Answer 1

NO_BACKSLASH_ESCAPES sql_mode.

Enabling this mode disables the use of the backslash character () as an escape character within strings and identifiers. With this mode enabled, backslash becomes an ordinary character like any other, and the default escape sequence for LIKE expressions is changed so that no escape character is used.

mysql> create table my_table (
    -> string  varchar(255) );
Query OK, 0 rows affected (0.34 sec)

mysql>
mysql> insert into my_table values
    -> ('4.4 (blah blah)');
Query OK, 1 row affected (0.07 sec)

mysql> select @@sql_mode;
+------------------------+
| @@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (1.318 sec)

mysql> set session sql_mode='NO_BACKSLASH_ESCAPES,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> UPDATE my_table SET string = '4.4 \(blah blah\)' WHERE string = '4.4 (blah blah)';
Query OK, 1 row affected (0.08 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from my_table;
+-------------------+
| string            |
+-------------------+
| 4.4 \(blah blah\) |
+-------------------+
1 row in set (0.02 sec)