concatenation - Can MySQL concatenate strings with ||

Question

I'm using sqlite3 for the moment, and hence concatenation strings using the || operator.

At some later date I'd like to shift to MySQL, and hence it would be nice if no changes to the code had to be made. I'd normally use concat() to concatenate in MySQL. Does || work too, or will I have to modify my code? Or is there any other solution?

I'm coding in Ruby on Rails 3.1, by the way.

Answer 1

The || works in MySQL as well but you need to set sql_mode to PIPES_AS_CONCAT.

Official Doc

Demo:

mysql> select c from tmp;
+------+
| c    |
+------+
| foo  |
| bar  |
+------+
2 rows in set (0.00 sec)

mysql> select c||' hi' from tmp;
+----------+
| c||' hi' |
+----------+
|        0 |
|        0 |
+----------+
2 rows in set, 2 warnings (0.00 sec)

mysql> set sql_mode=PIPES_AS_CONCAT;
Query OK, 0 rows affected (0.00 sec)

mysql> select c||' hi' from tmp;
+----------+
| c||' hi' |
+----------+
| foo hi   |
| bar hi   |
+----------+
2 rows in set (0.00 sec)