Pat R Ellery Pat R Ellery - 17 days ago 11
MySQL Question

MySQL procedure show a "select" statement twice

I have lots of stored procedures doing a bunch of alter, create etc... and I want to see on each procedure which one are being executed.

DELIMITER |

DROP PROCEDURE IF EXISTS test1|

CREATE PROCEDURE test1()
BEGIN
IF /*select ... */ THEN
BEGIN
SELECT "hello world";
END;
END IF;
END|

DROP PROCEDURE IF EXISTS test2|

CREATE PROCEDURE test2()
BEGIN
IF /*select ... */ THEN
BEGIN
SELECT "hello world 2";
END;
END IF;
END|

DELIMITER ;
CALL test1();
CALL test2();


then i call it:

cat proc.sql | mysql -uroot -p mydb


output:

hello world
hello world
hello world 2
hello world 2

root@localhost:test> select version();
+-------------------------+
| version() |
+-------------------------+
| 5.5.44-0ubuntu0.14.04.1 |
+-------------------------+


why it shows twice?

Answer

Column name and value is displayed.

mysql> CALL test1();
+-------------+
| hello world | <- Column name
+-------------+
| hello world | <- Value
+-------------+
1 row in set (0,00 sec)

If you assign an alias to your column:

...
SELECT "hello world" `column_name`;
...

output:

column_name
hello world

UPDATE

Use:

$ cat proc.sql | mysql -uroot -p -N mydb

output:

hello world
hello world 2

See: --skip-column-names, -N

Comments