如下, 谢谢!-- /**
-- * this is a SP of dengx.blog_user table.
-- * table structure: blog_user(uid, account, password, key, lastime)
-- */DELIMITER //DROP FUNCTION IF EXISTS dengx.user_register;CREATE FUNCTION dengx.user_register(
user_account,varchar(50),
user_password,varchar(50),
user_key,int(5),
user_lastime,int(10)
)RETURNS int(100)BEGIN
DECLARE user_old_id int default 0;
DECLARE user_new_id int(100);
SELECT uid INTO user_has_id FROM blog_user WHERE account = user_account ; IF NOT user_old_id THEN
INSERT INTO blog_user(account,password,key,lastime)VALUES(user_account,user_password,user_key,user_lastime);
SELECT LAST_INSERT_ID() INTO user_new_id;
RETURN user_new_id;
END IF;
RETURN 0;
END;//DELIMITER;
-- * this is a SP of dengx.blog_user table.
-- * table structure: blog_user(uid, account, password, key, lastime)
-- */DELIMITER //DROP FUNCTION IF EXISTS dengx.user_register;CREATE FUNCTION dengx.user_register(
user_account,varchar(50),
user_password,varchar(50),
user_key,int(5),
user_lastime,int(10)
)RETURNS int(100)BEGIN
DECLARE user_old_id int default 0;
DECLARE user_new_id int(100);
SELECT uid INTO user_has_id FROM blog_user WHERE account = user_account ; IF NOT user_old_id THEN
INSERT INTO blog_user(account,password,key,lastime)VALUES(user_account,user_password,user_key,user_lastime);
SELECT LAST_INSERT_ID() INTO user_new_id;
RETURN user_new_id;
END IF;
RETURN 0;
END;//DELIMITER;
这个工具一点开就关了, 不知道什么回事, 平时创建表也是用phpmyadmin导入的, 用这个导入应该没问题, 我怀疑是不是代码写错在那里
先进行WINDOWS的命令行,(也就是俗称的DOS),然后用命令 mysql -uroot -pxxxx
DELIMITER //DROP FUNCTION IF EXISTS dengx.user_register;CREATE FUNCTION dengx.user_register(
user_account,varchar(50),
user_password,varchar(50),
user_key,int(5),
user_lastime,int(10)
)RETURNS int(100)BEGIN
DECLARE user_old_id int default 0;
DECLARE user_new_id int(100);
SELECT uid INTO user_old_id FROM blog_user WHERE account = user_account ; IF NOT user_old_id THEN
INSERT INTO blog_user(account,password,key,lastime)VALUES(user_account,user_password,user_key,user_lastime);
SELECT LAST_INSERT_ID() INTO user_new_id;
RETURN user_new_id;
END IF;
RETURN 0;
END;//DELIMITER;
DELIMITER //DROP FUNCTION IF EXISTS dengx.user_register;CREATE FUNCTION dengx.user_register(
user_account varchar(50),
user_password varchar(50),
user_key int(5),
user_lastime int(10)
)RETURNS intBEGIN
DECLARE user_old_id int default 0;
DECLARE user_new_id int;
SELECT uid INTO user_old_id FROM blog_user WHERE account = user_account ; IF user_old_id != 0 THEN
INSERT INTO blog_user(account,password,ukey,lastime) VALUES(user_account,user_password,user_key,user_lastime);
SELECT LAST_INSERT_ID() INTO user_new_id;
RETURN user_new_id;
END IF;
RETURN 0;
END;
//
DELIMITER ;修改了一下,试一下看能不能运行。
INSERT INTO blog_user(account,password,ukey,lastime) VALUES(user_account,user_password,user_key,user_lastime);上面这条语句的ukey改成key,不知道你怎么能用key做字段名的,key是关键字。
user_account varchar( 50 ) ,
user_password varchar( 50 ) ,
user_key int( 5 ) ,
user_lastime int( 10 )
) RETURNS int BEGIN DECLARE user_old_id int( 100 ) default 0;MySQL said: Documentation
#1064 - 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 ''user_register'(
user_account varchar(50),
user_password varchar(50),
u' at line 1
用你的代码粘贴, 报错#1064 - 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 'CREATE FUNCTION dengx.user_register( user_account varchar(50), user_passwo' at line 3
user_account varchar(50),
user_password varchar(50),
user_key int(5),
user_lasttime int(10)
)RETURNS intBEGIN
DECLARE user_old_id int default 0;
DECLARE user_new_id int default 0;
SELECT uid INTO user_old_id FROM blog_user WHERE account = user_account ; IF user_old_id = 0 THEN
INSERT INTO blog_user(account,password,salt,lastime) VALUES(user_account,user_password,user_key,user_lasttime);
SELECT LAST_INSERT_ID() INTO user_new_id;
RETURN user_new_id;
END IF;
RETURN 0;
END;
//
DELIMITER ;
mysql> select * from blog_user;
+------+---------+-----------+------+------------+
| uid | account | password | ukey | lasttime |
+------+---------+-----------+------+------------+
| 1 | 200 | zhoupuyue | key | 2009-08-24 |
| 2 | 530 | csdn | csdn | 2009-05-14 |
+------+---------+-----------+------+------------+
2 rows in set (0.00 sec)mysql> select user_register('200','csdn1','csdn','2007-04-25');
+--------------------------------------------------+
| user_register('200','csdn1','csdn','2007-04-25') |
+--------------------------------------------------+
| 0 |
+--------------------------------------------------+
1 row in set, 2 warnings (0.00 sec)
user_account varchar( 50 ) ,
user_password varchar( 50 ) ,
user_key int( 5 ) ,
user_lasttime int( 10 )
) RETURNS int BEGIN DECLARE user_old_id int default 0;MySQL said: Documentation
#1064 - 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 9
`uid` int(100) NOT NULL AUTO_INCREMENT,
`account` varchar(50) NOT NULL,
`password` varchar(50) NOT NULL,
`salt` int(5) NOT NULL,
`lastime` int(10) NOT NULL,
PRIMARY KEY (`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
贴出你的create table 语句和 create function 语句,还有你的mysql 版本。
CREATE TABLE IF NOT EXISTS `blog_user` (
`uid` int(100) NOT NULL AUTO_INCREMENT,
`account` varchar(50) NOT NULL,
`password` varchar(50) NOT NULL,
`salt` int(5) NOT NULL,
`lastime` int(10) NOT NULL,
PRIMARY KEY (`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;-- 储存过程
DELIMITER //DROP FUNCTION IF EXISTS dengx.user_register;CREATE FUNCTION dengx.user_register(
user_account varchar(50),
user_password varchar(50),
user_salt int(5),
user_lastime int(10)
)RETURNS intBEGIN
DECLARE user_old_id int default 0;
DECLARE user_new_id int default 0;
SELECT uid INTO user_old_id FROM blog_user WHERE account = user_account ; IF user_old_id = 0 THEN
INSERT INTO blog_user(account,password,salt,lastime) VALUES(user_account,user_password,user_salt,user_lastime);
SELECT LAST_INSERT_ID() INTO user_new_id;
RETURN user_new_id;
END IF;
RETURN 0;
END;
//
DELIMITER ;
版本
mysql: Server version: 5.1.29
phpmyadmin: Version information: 3.0.1.1
后来我测试了一下
create procedure dengx.test()
begin
select * from dengx.blog_category
end ;
--错误create procedure dengx.test() select * from dengx.blog_category--正常
DROP FUNCTION IF EXISTS user_register//建议你也同样,到mysql的命令工具下进行测试,这样看得比较清楚。
mysql> CREATE TABLE IF NOT EXISTS `blog_user` (
-> `uid` int(100) NOT NULL AUTO_INCREMENT,
-> `account` varchar(50) NOT NULL,
-> `password` varchar(50) NOT NULL,
-> `salt` int(5) NOT NULL,
-> `lastime` int(10) NOT NULL,
-> PRIMARY KEY (`uid`)
-> );
Query OK, 0 rows affected (0.08 sec)mysql> set global log_bin_trust_function_creators=ON;
Query OK, 0 rows affected (0.00 sec)mysql> select @@log_bin_trust_function_creators;
+-----------------------------------+
| @@log_bin_trust_function_creators |
+-----------------------------------+
| 1 |
+-----------------------------------+
1 row in set (0.00 sec)mysql> DELIMITER //
mysql>
mysql> DROP FUNCTION IF EXISTS user_register//
Query OK, 0 rows affected, 1 warning (0.00 sec)mysql>
mysql> CREATE FUNCTION user_register(
-> user_account varchar(50),
-> user_password varchar(50),
-> user_salt int(5),
-> user_lastime int(10)
-> )RETURNS int
->
-> BEGIN
-> DECLARE user_old_id int default 0;
-> DECLARE user_new_id int default 0;
-> SELECT uid INTO user_old_id FROM blog_user WHERE account = user_account ;
->
-> IF user_old_id = 0 THEN
-> INSERT INTO blog_user(account,password,salt,lastime) VALUES(user_account,user_password,user_salt,user_lastime);
-> SELECT LAST_INSERT_ID() INTO user_new_id;
-> RETURN user_new_id;
-> END IF;
-> RETURN 0;
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ;
mysql>
mysql> select version();
+----------------------+
| version() |
+----------------------+
| 5.1.33-community-log |
+----------------------+
1 row in set (0.00 sec)mysql>
begin
select now();
select 'aaa';
end 不加 delimiter
错误, 不要begin end关键字就对
是的, 在phpmyadmin中, 可以不要"delimiter //" 这个, 而且里面只用" ; " 作为分隔符,但是phpmyadmin好像不支持"begin, end"语法 , 而导至这个问题出错的原因
不是不要 //,而是你要在PHPMYADMIN的设置中将 delimiter 改成 // ,否则它还是会以为 ; 为提交符。
底部的一个小框内。
是有个Delimiter ; 的东西 , 把它改成了 // 接着怎么样 .... .... 代码都是报错
create procedure sp_test()
begin
select now();
select 'aaa';
end //
user_account varchar(50),
user_password varchar(50),
user_salt int(5),
user_lastime int(10)
)RETURNS intBEGIN
DECLARE user_old_id int default 0;
DECLARE user_new_id int default 0;
SELECT uid INTO user_old_id FROM dengx.blog_user WHERE account = user_account ; IF user_old_id = 0 THEN
INSERT INTO dengx.blog_user(account,password,salt,lastime) VALUES(user_account,user_password,user_salt,user_lastime);
SELECT LAST_INSERT_ID() INTO user_new_id;
RETURN user_new_id;
END IF;
RETURN 0;
END;
user_account varchar(50),
user_password varchar(50),
user_salt int(5),
user_lastime int(10)
)RETURNS intBEGIN
DECLARE user_old_id int default 0;
DECLARE user_new_id int default 0;
SELECT uid INTO user_old_id FROM dengx.blog_user WHERE account = user_account ; IF user_old_id = 0 THEN
INSERT INTO dengx.blog_user(account,password,salt,lastime) VALUES(user_account,user_password,user_salt,user_lastime);
SELECT LAST_INSERT_ID() INTO user_new_id;
RETURN user_new_id;
END IF;
RETURN 0;
END //
非常感谢acmain和zhoupuyue当了我一回MYSQL的SP入门老师
2 phpmyadmin里提交sql, 要把提交符" ; " 改成 " // " , 不同语句要分开提交.
(例如, drop, create等)