MySQL client tool and how to output newlines

This blog posts explains how to add a new line in strings in MySQL Stored Procedures and how to output the result using the MySQL client tool.

Today I was fooling around with some stored procedure making it more fancy and stuff. What I wanted was the OUT variable to contain a newline. Easy of course, using CONCAT:

mysql> SELECT CONCAT('foo','\n','bar');
+--------------------------+
| CONCAT('foo','\n','bar') |
+--------------------------+
| foo
bar                  |
+--------------------------+

Now, if you concat strings in a stored procedure, it doesn’t work as expected when you run it through the MySQL client tool mysql:

DELIMITER //
CREATE PROCEDURE sp1(OUT pres VARCHAR(6000))
BEGIN
  SET pres = CONCAT('foo','\n','bar');
END;
//
DELIMITER ;

SET @res = 'foo ';
CALL sp1(@res);
SELECT @res;

When we execute it, we get this:

$ mysql -N test < foo.sql
foo\nbar

What on earth is wrong?

After some looking, we found a not so often used option called --raw. This produces the the desired effect:

$ mysql -Nr test < foo.sql
foo
bar

But that’s not all! Use \G when selecting the OUT-variable and it is also working. The output is not so useful though.

Ah.. The things you find out while having the day off..