How to echo print statements while executing a sql script

Question

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?

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

Cory Klein · Accepted Answer · 2015-06-10 19:50:30Z

74

This will give you are simple print within a sql script:

select 'This is a comment' AS '';

Alternatively, this will add some dynamic data to your status update if used directly after an update, delete, or insert command:

select concat ("Updated ", row_count(), " rows") as '';

edited Jun 10, 2015 at 19:50

Cory Klein

56.6k48 gold badges207 silver badges262 bronze badges

answered Nov 16, 2011 at 6:58

blankabout

2,6473 gold badges20 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Joe Over a year ago

What should I do if I have to capture the number of rows affected from a certain update/insert/delete statement.

John Over a year ago

This adds an extra white space row. Is there a way to do this with out the extra row?

Aaron D. Marasco Over a year ago

@John I have used Attn or Note.

tgf · Accepted Answer · 2019-12-04 08:55:44Z

41

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

edited Dec 4, 2019 at 8:55

tgf

3,3072 gold badges22 silver badges32 bronze badges

answered May 22, 2016 at 18:28

Craig Wayne

5,1674 gold badges40 silver badges52 bronze badges

3 Comments

UncaAlby Over a year ago

So long as you just need a message without any parameters, this works perfectly.

flow2k Over a year ago

This works great. You say "provided the script is run via a command line" - how else would this be run? That is, under what circumstances would this not work?

flow2k Over a year ago

This feature is documented at dev.mysql.com/doc/refman/8.0/en/mysql-commands.html.

lrkwz · Accepted Answer · 2020-07-17 09:25:58Z

11

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

answered Jul 17, 2020 at 9:25

lrkwz

6,6124 gold badges43 silver badges67 bronze badges

1 Comment

razzed Over a year ago

Having using Postgres most recently, psql -e or -E is the same thing; not sure what else -v will output, but this is what I was looking for

useless · Accepted Answer · 2016-05-13 09:49:01Z

6

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');

answered May 13, 2016 at 9:49

useless

1,90418 silver badges19 bronze badges

1 Comment

UncaAlby Over a year ago

This seems a lot of extra steps, especially compared to solution from Craig Wayne

Karl Bunch · Accepted Answer · 2020-09-19 11:48:53Z

6

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)

answered Sep 19, 2020 at 11:48

Karl Bunch

1612 silver badges3 bronze badges

Comments

Mithun Sasidharan · Accepted Answer · 2011-11-16 06:57:51Z

0

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

answered Nov 16, 2011 at 6:57

Mithun Sasidharan

21k10 gold badges38 silver badges53 bronze badges

1 Comment

Joe Over a year ago

I should also mention that this script could be run from a Windows or a Linux environment

Collectives™ on Stack Overflow

How to echo print statements while executing a sql script

6 Answers 6

3 Comments

3 Comments

1 Comment

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

3 Comments

3 Comments

1 Comment

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related