MySQL Storage process first encountered

I wrote a simple stored procedure with Notepad as follows:

delimiter$$

CREATE PROCEDURE Show_users ()

Comment ' View all information in the user table in the market database '

Begin

select * from user;

end$$

Delimiter

Save As Show_users.sql

Execute (show_users.sql in working directory, if not please add absolute path):

Mysql> Source Show_users.sql

An error message occurs:

Error 1064 (42000): You have a error in your SQL syntax; Check the manual that

Corresponds to your MySQL server version for the right syntax to use near ' Delim

iter$ $create Procedure Show_users ()

Comment '?? Market?????? Е?user?????? ' at line 1

Error 1064 (42000): You have a error in your SQL syntax; Check the manual that

Corresponds to your MySQL server version for the right syntax to use near ' end$$

Delimiter ' at line 1

The first is to see the garbled, we can first look at the database encoding information:

Mysql> Show variables like ' char% ';

found to be MySQL data can be in addition to the file system encoding is binary other encoding is UTF8(mysql put utf-8 alias is set to UTF8)

We write a stored procedure in Notepad and import it, and the default encoding for Notepad is ANSI, so let's change the way our stored procedure files Show_users.sql encoded. You can choose Save as under the Notepad File menu , and the encoding will be utf-8.

Let's take a look at the error message:

Error 1064 (42000): You have a error in your SQL syntax; Check the manual that

Corresponds to your MySQL server version for the right syntax to use near ' end$$

Delimiter ' at line 1

The information we get is a syntax error, near End$$. We do not know for a moment what is wrong with 1064, and we use the tools provided by MySQL Perror to see what the error code 1064 stands for.

C:\users\administrator>perror 1064 (please ensure that the bin directory of MySQL is in the system environment variable, if not please add absolute path)

The first one is the error code information for MySQL1064, and the second is the 1064 error code information for Windows.

Er_parse_error parsing errors, indicating what basic grammatical errors we have made, but end$$ there seems to be no mistake. The entire stored procedure is also very simple, we can simply analyze, other places are SQL statements, there should be no error. Error is the place is end$$, we can guess either this place is wrong, or the beginning of delimiter$$ error, end$$ we can not make any mistakes, so we start from the delimiter$$ analysis.

delimiter is a key word for MySQL, which is used to modify the terminator of the SQL statement, which means to modify the terminator of the SQL statement to $$ in order to avoid terminator conflicts with stored procedures.

try to find out many times, since delimiter is a keyword, then delimiter and $$ between should have a space apart? Try adding a space between delimiter and $$ to find that the problem is really a space between delimiter and $$ . Then thinkabout it, delimiter is a key word, if and $ directly without a space apart,MySQL should have it resolved to the delimiter$$ identifier.

The last problem is solved, we have to re-execute:

Mysql> Source Show_users.sql

ERROR 1044 (42000): Access denied for the user ' Tim ' @ '% ' to the database ' market '

With This error message, it is easy to know that the user Tim does not have permission to use the administrator user to give the Tim user permission to create the stored procedure.

Mysql> Grant create routine on market.* to Tim;

Flush privileges; (Refresh the System permissions table, and sometimes you need to log back in)

Re-execute again:

This time we find that there is no problem, let's take a look:

Call the stored procedure (the permission to perform the Save (execute) stored procedure, as above, as assigned by the Administrator account):

to this end finally completed the execution of the first stored procedure, we found that this is not an easy process, but we also know some other practical things, I believe will slowly familiar with, quickly locate the wrong.

MySQL Storage process first encountered