[mysql] How to echo print statements while executing a sql script

We have a simple sql script which needs to be executed against a MySQL database and we would like print log statements on the progress of the script (e.g. Inserted 10 records into foo or Deleted 5 records from bar). How do we do this?

  1. I would like to know the syntax to be used for insert/update/delete statements.
  2. How do I know about the number of rows affected by my statement(s).
  3. I would also like to control printing them using a ECHO off or on command at the top of the script.
  4. The script should be portable across Windows / Linux OS.

This question is related to mysql sql

The answer is


Just to make your script more readable, maybe use this proc:

DELIMITER ;;

DROP PROCEDURE IF EXISTS printf;
CREATE PROCEDURE printf(thetext TEXT)
BEGIN

  select thetext as ``;

 END;

;;

DELIMITER ;

Now you can just do:

call printf('Counting products that have missing short description');

What about using mysql -v to put mysql client in verbose mode ?


I don't know if this helps:

suppose you want to run a sql script (test.sql) from the command line:

mysql < test.sql

and the contents of test.sql is something like:

SELECT * FROM information_schema.SCHEMATA;
\! echo "I like to party...";

The console will show something like:

CATALOG_NAME    SCHEMA_NAME            DEFAULT_CHARACTER_SET_NAME      
         def    information_schema     utf8
         def    mysql                  utf8
         def    performance_schema     utf8
         def    sys                    utf8
I like to party...

So you can execute terminal commands inside an sql statement by just using \!, provided the script is run via a command line.

\! #terminal_commands

For mysql you can add \p to the commands to have them print out while they run in the script:

SELECT COUNT(*) FROM `mysql`.`user`
\p;

Run it in the MySQL client:

mysql> source example.sql
--------------
SELECT COUNT(*) FROM `mysql`.`user`
--------------

+----------+
| COUNT(*) |
+----------+
|       24 |
+----------+
1 row in set (0.00 sec)

You can use print -p -- in the script to do this example :

#!/bin/ksh
mysql -u username -ppassword -D dbname -ss -n -q |&
print -p -- "select count(*) from some_table;"
read -p get_row_count1
print -p -- "select count(*) from some_other_table;"
read -p get_row_count2
print -p exit ;
#
echo $get_row_count1
echo $get_row_count2
#
exit