what is the correct syntax for executing .sql script in MySQL command line?

Question

I am confused. From references I have seen online, the command to execute a text file script is this:

mysql> --user=root --password=admin --database=zero <query.sql

However when I ran this, the command line said theres an error with mySQL syntax (error 1064). I saved the query.sql script file within the C:program files...\MYSQL\MYSQL Server5.1.. (whichever folder directory that contains the mySQL command line terminal .exe)

I then did this:

 mysql> USE db1 \g
 mysql> source <query.sql \g

It also doesnt work; command line gave me the same error. mySQL version I have is different than other versions I have seen. As you can see, you have to add '\g' at the end of every query.

Please help, and let me know if the description is not very clear..thx

EDITED: So this is the code I have inside the query.sql:

CREATE TABLE IF NOT EXISTS 'db1'(
'id' int(255) NOT NULL auto_increment,
'date' date NOT NULL,
'title' varchar(255) NOT NULL,
'introtext' text NOT NULL,
'maintext' text NOT NULL,
PRIMARY KEY ('id')
)

The error message isn't saying you're executing the script wrong, it says a statement in the file has a syntax error. For us to say exactly why it's wrong and how to fix it, please reduce the file to a minimal sample and include it in your question. — outis
– outis, Commented May 22, 2011 at 22:00
1064: Error: 1064 SQLSTATE: 42000 (ER_PARSE_ERROR) Message: %s near '%s' at line %d. Errors starting with a '1' are errors server side. Your script is working fine, or your error would have started with a '2'. Like @outis said it's probably a syntax error in your query. — Johan
– Johan, Commented May 22, 2011 at 22:05
@outis : That was what I thought the first time. But I then doubted it since I pretty much copy-paste the exact SQL code from a tutorial practice blog.. — Benny Tjia
– Benny Tjia, Commented May 22, 2011 at 22:13
@Benny: Remove the quotes: 'db1' and only use backquotes `db1` if necessary. — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented May 22, 2011 at 22:15
I suggest you use Query Browser or Workbench for start, so you have a graphical interface and can use File->Open Script. — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented May 23, 2011 at 0:13

rid · Accepted Answer · 2011-05-22 21:57:30Z

5

You can run an SQL file from within the client with:

\. query.sql

Alternatively, if you're not already in the client, you can use the following from the command line:

mysql --user=root --password=admin --database=zero < query.sql

answered May 22, 2011 at 21:57

rid

64k31 gold badges159 silver badges201 bronze badges

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

2 Comments

Benny Tjia Over a year ago

right, I also had tried that before..for this one, I had "error:2 ERROR:no query specified".

rid Over a year ago

You should not escape the table and field names with single quotes ('), but rather with backquotes (`).

ypercubeᵀᴹ · Accepted Answer · 2011-05-22 22:17:01Z

2

Remove the quotes: 'db1'. Use backquotes where necessary, like for field called date to identify it from type date. And add a ; at the end of the statement:

CREATE TABLE IF NOT EXISTS db1(
  id int(255) NOT NULL auto_increment,
  `date` date NOT NULL,
  title varchar(255) NOT NULL,
  introtext text NOT NULL,
  maintext text NOT NULL,
  PRIMARY KEY (id)
) ;

answered May 22, 2011 at 22:17

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

4 Comments

outis Over a year ago

@BennyTjia: To expand on the point, single quotes in MySQL delimit values, while backticks delimit identifiers. 'db1' in the statement is a string, while (after changing quotes to backticks) `db1` would be a table named "db1".

Benny Tjia Over a year ago

I tried changing the code, and now the error I have is this-> ERROR: Failed to open file '<d', error: 22 ERROR: No query specified. What I have been wondering also is whether i save the .sql script in the right directory..Does anybody here know where the script should be saved if we want to execute it from the terminal?

rid Over a year ago

@BennyTjia, wherever you save the file, as long as you use the full path to the file while importing, you should be fine. To import a file from inside the client, you should not use the character <, but rather the path to the file directly. Refer to my answer for how to import an SQL file containing correct syntax.

CodeMed Over a year ago

@ypercube I have a similar question. Can you please help me with it? I am trying to run external sql files that create and load tables in an MySQL database. Here is the link: stackoverflow.com/questions/22411995/…

King O · Accepted Answer · 2016-10-03 20:43:32Z

0

You need to specify the DB so a statement like:-

USE (DATABASE NAME)

So above replace the (DATABASE NAME) with the name of the Database

answered Oct 3, 2016 at 20:43

King O

1

Collectives™ on Stack Overflow

what is the correct syntax for executing .sql script in MySQL command line?

3 Answers 3

2 Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related