Executing SQL Statements from a Text File on MySQL Client

MySQLMySQLi Database

Let us understand how SQL statements can be executed from a text file on the MySQL client. The mysql client is generally used in interactive way. Let us see an example of the same −

shell> mysql db_name

Place SQL Satements in a Text File

It is also possible to put the SQL statements in a file and then tell mysql to read the input from that specific file. To do this, a text file text_file is first created.

This text file would contain the statements that need to be executed. Let us take an example to understand the same −

shell> mysql db_name < text_file

If a USE db_name statement is placed as the first statement in the text file, it is unnecessary to specify the database name on the command line. Let us take an example to understand the same −

shell> mysql < text_file

If mysql is already running, an SQL script file can be executed using the source command or \. command. Let us take an example to understand the same −

mysql> source file_name
mysql> \. file_name

Sometimes the user may want the script to display progress information to them. For this insert statements like that given below can be used −

SELECT '<info_to_display>' AS ' ';

Output

<info_to_display>

The mysql can also be invoked with the help of --verbose option. This causes every statement to be displayed before the result it produces.

  • The mysql ignores Unicode byte order mark (BOM) characters at the beginning of input files.

  • Before this functionality, they were read and sent over to the server, thereby resulting in a syntax error.

  • The present of a BOM doesn’t cause mysql to change its default character set.

  • To do this, mysql can be invoked with an option like --default-character-set=utf8.

raja
Published on 10-Mar-2021 17:23:03
Advertisements