测试代码:
#简单创建两个表和一个存储过程
drop database if exists webdb;
create database webdb;
use webdb;
create table users_message
(
english_name varchar(50) primary key,
chinese_name varchar(20)
);
insert into users_message values('Thomas Chan', '陈生');
insert into users_message values('Pangzi Long', '胖子龙');
insert into users_message values('Jacky Chan', '成龙');
DROP TABLE IF EXISTS webdb.t_users;
create table if not exists webdb.t_users
(
user_name varchar(20) collate utf8_unicode_ci primary key,
password_md5 varchar(50) collate utf8_unicode_ci not null,
email varchar(30) collate utf8_unicode_ci
) engine=InnoDB default charset=utf8 collate=utf8_unicode_ci;
insert into t_users values('Thomas Chan', '123', '[email protected]');drop procedure if exists test;
#delimiter $$
CREATE PROCEDURE test()
begin
select * from webdb.t_users;
end;
#delimiter;
call test();在存储过程这个位置mysql命令行老报错
而且还有个问题,MySQL-Front成功运行怎么不显示结果?MySQL-Front没显示结果的功能?
#简单创建两个表和一个存储过程
drop database if exists webdb;
create database webdb;
use webdb;
create table users_message
(
english_name varchar(50) primary key,
chinese_name varchar(20)
);
insert into users_message values('Thomas Chan', '陈生');
insert into users_message values('Pangzi Long', '胖子龙');
insert into users_message values('Jacky Chan', '成龙');
DROP TABLE IF EXISTS webdb.t_users;
create table if not exists webdb.t_users
(
user_name varchar(20) collate utf8_unicode_ci primary key,
password_md5 varchar(50) collate utf8_unicode_ci not null,
email varchar(30) collate utf8_unicode_ci
) engine=InnoDB default charset=utf8 collate=utf8_unicode_ci;
insert into t_users values('Thomas Chan', '123', '[email protected]');drop procedure if exists test;
#delimiter $$
CREATE PROCEDURE test()
begin
select * from webdb.t_users;
end;
#delimiter;
call test();在存储过程这个位置mysql命令行老报错
而且还有个问题,MySQL-Front成功运行怎么不显示结果?MySQL-Front没显示结果的功能?
下面是我的实验
一:
For server side help, type 'help contents'mysql> delimiter $$
mysql> CREATE PROCEDURE test()
-> begin
-> select * from t_users;
-> end $$
ERROR 1304 (42000): PROCEDURE test already exists
mysql> delimiter $$
mysql> CREATE PROCEDURE test1()
-> begin
-> select * from t_users;
-> end $$
Query OK, 0 rows affected (0.00 sec)mysql> delimiter;
-> help;
-> $$
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'delim
iter;
help' at line 1
mysql> CREATE PROCEDURE test1()
-> begin
-> select * from t_users;
-> end $$
Query OK, 0 rows affected (0.00 sec)
说明这种方式可以成功创建存储过程
而mysql> delimiter;
-> help;
-> $$
说明在使用$$后
只有使用$$,才能显示结果,而且如果在$$使用help命令,则直接报错
重启mysql命令端
输入
mysql> use webdb;
Database changed
mysql> delimiter $$
mysql> CREATE PROCEDURE test7()
-> begin
-> select * from t_users;
-> end
-> delimiter;
->
说明delimiter;不能结束这种控制
delimiter; 分号之前应该有个空格吧
mysql> use webdb;
Database changed
mysql> delimiter $$
mysql> CREATE PROCEDURE test7()
-> begin
-> select * from t_users;
-> end
-> delimiter ;
->
-> $$
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'delim
iter' at line 5
mysql> help;
->
-> $$
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '' at
line 1
mysql>
当有第一个$$后,只有再次输入$$,mysql命令才开始执行第一个$$后的语句
而且在报错后输入help;后,没有出现帮助信息
说明$$的控制范围不仅仅是在两对$$中间,它在承上启下,控制着接下来的语句
而且在这种控制下help;命令会直接报错
mysql> CREATE PROCEDURE test9()
-> begin
-> select * from t_users;
-> end $$
Query OK, 0 rows affected (0.00 sec)mysql> delimiter;
->
---------------------------------------
mysql> use webdb;
Database changed
mysql> delimiter $$
mysql> CREATE PROCEDURE test8()
-> begin
-> select * from t_users;
-> end $$
Query OK, 0 rows affected (0.05 sec)mysql> delimiter ;
mysql> help;For information about MySQL products and services, visit:
http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
http://dev.mysql.com/
To buy MySQL Network Support, training, or other products, visit:
https://shop.mysql.com/
在这里,help的帮助信息出来了,说明delimiter ;中间确实要加空格,而且end后要接一个$$