You have an error in your SQL syntax问题解决方法

最新推荐文章于 2024-04-28 09:26:46 发布

nonamelake

最新推荐文章于 2024-04-28 09:26:46 发布

阅读量2.6w

点赞数 1

分类专栏： mysql

本文链接：https://blog.csdn.net/weixin_42612178/article/details/115559789

版权

mysql 专栏收录该内容

6 篇文章 1 订阅

订阅专栏

1. 问题现象

1.1 最近在学jmeter的分析和优化，需要用mysql准备测试数据，执行如下语句：


		BEGIN
			DECLARE username CHAR(20);
			DECLARE usermail CHAR(20);
			DECLARE i INT DEFAULT 1;
			WHILE i<= 400 DO
				set username = CONCAT('test',LPAD(i,3,'0'));
				set usermail = CONCAT(username,'@test.com');
				INSERT INTO jforum_users VALUES (i,'1',username,'nopass','0','0',NULL,'7/4/2021 18:34:55',NULL,'0',NULL,NULL,NULL,'%d/%M/%Y %H:%i','0','0',NULL,NULL,'0','1','0','1','1','1','1','1','1','0','0','1','1','0',NULL,'0',useremail,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1',NULL,NULL,NULL);
				INSERT INTO jforum_user_groups VALUES (3,i);
				SET i = i + 1;
			END WHILE;
		END

1.2 结果报错如下：

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 ‘DECLARE username VARCHAR(20)’ at line 2

2. 解决方法

2.1 在网上找了好久也没有找到一个有用的案例，没办法，找不到案例就从头学一下吧，在如下网站看了一下https://www.runoob.com/w3cnote/mysql-stored-procedure.html，才了解《MySQL存储过程》基本的写法，哎，现在的书代码都不写全啊！！！
2.2 另外，可以使用如下语句先查询每个字段的信息，比如类型，是否可以为NULL等，对于不想填写的字段可以自己用命令修改字段的属性。

mysql> use jforum;
Database changed
mysql> show tables;
+-------------------------+
| Tables_in_jforum        |
+-------------------------+
| jforum_api              |
| jforum_attach           |
| jforum_attach_desc      |
| jforum_attach_quota     |
| jforum_banlist          |
| jforum_banner           |
| jforum_bookmarks        |
| jforum_categories       |
| jforum_config           |
| jforum_extension_groups |
| jforum_extensions       |
| jforum_forums           |
| jforum_forums_watch     |
| jforum_groups           |
| jforum_karma            |
| jforum_mail_integration |
| jforum_moderation_log   |
| jforum_posts            |
| jforum_posts_text       |
| jforum_privmsgs         |
| jforum_privmsgs_text    |
| jforum_quota_limit      |
| jforum_ranks            |
| jforum_role_values      |
| jforum_roles            |
| jforum_sessions         |
| jforum_smilies          |
| jforum_themes           |
| jforum_topics           |
| jforum_topics_watch     |
| jforum_user_groups      |
| jforum_users            |
| jforum_vote_desc        |
| jforum_vote_results     |
| jforum_vote_voters      |
| jforum_words            |
+-------------------------+
36 rows in set (0.00 sec)

mysql> show columns from jforum_users;
+-----------------------+--------------+------+-----+----------------+----------------+
| Field                 | Type         | Null | Key | Default        | Extra          |
+-----------------------+--------------+------+-----+----------------+----------------+
| user_id               | int(11)      | NO   | PRI | NULL           | auto_increment |
| user_active           | tinyint(1)   | YES  |     | NULL           |                |
| username              | varchar(50)  | NO   |     |                |                |
| user_password         | varchar(32)  | NO   |     |                |                |
| user_session_time     | bigint(20)   | YES  |     | 0              |                |
| user_session_page     | int(11)      | NO   |     | 0              |                |
| user_lastvisit        | datetime     | YES  |     | NULL           |                |
| user_regdate          | datetime     | YES  |     | NULL           |                |
| user_level            | tinyint(4)   | YES  |     | NULL           |                |
| user_posts            | int(11)      | NO   |     | 0              |                |
| user_timezone         | varchar(5)   | NO   |     |                |                |
| user_style            | tinyint(4)   | YES  |     | NULL           |                |
| user_lang             | varchar(255) | NO   |     |                |                |
| user_dateformat       | varchar(20)  | NO   |     | %d/%M/%Y %H:%i |                |
| user_new_privmsg      | int(11)      | NO   |     | 0              |                |
| user_unread_privmsg   | int(11)      | NO   |     | 0              |                |
| user_last_privmsg     | datetime     | YES  |     | NULL           |                |
| user_emailtime        | datetime     | YES  |     | NULL           |                |
| user_viewemail        | tinyint(1)   | YES  |     | 0              |                |
| user_attachsig        | tinyint(1)   | YES  |     | 1              |                |
| user_allowhtml        | tinyint(1)   | YES  |     | 0              |                |
| user_allowbbcode      | tinyint(1)   | YES  |     | 1              |                |
| user_allowsmilies     | tinyint(1)   | YES  |     | 1              |                |
| user_allowavatar      | tinyint(1)   | YES  |     | 1              |                |
| user_allow_pm         | tinyint(1)   | YES  |     | 1              |                |
| user_allow_viewonline | tinyint(1)   | YES  |     | 1              |                |
| user_notify           | tinyint(1)   | YES  |     | 1              |                |
| user_notify_always    | tinyint(1)   | YES  |     | 0              |                |
| user_notify_text      | tinyint(1)   | YES  |     | 0              |                |
| user_notify_pm        | tinyint(1)   | YES  |     | 1              |                |
| user_popup_pm         | tinyint(1)   | YES  |     | 1              |                |
| rank_id               | int(11)      | YES  |     | 0              |                |
| user_avatar           | varchar(100) | YES  |     | NULL           |                |
| user_avatar_type      | tinyint(4)   | NO   |     | 0              |                |
| user_email            | varchar(255) | NO   |     |                |                |
| user_icq              | varchar(15)  | YES  |     | NULL           |                |
| user_website          | varchar(255) | YES  |     | NULL           |                |
| user_from             | varchar(100) | YES  |     | NULL           |                |
| user_sig              | text         | YES  |     | NULL           |                |
| user_sig_bbcode_uid   | varchar(10)  | YES  |     | NULL           |                |
| user_aim              | varchar(255) | YES  |     | NULL           |                |
| user_yim              | varchar(255) | YES  |     | NULL           |                |
| user_msnm             | varchar(255) | YES  |     | NULL           |                |
| user_occ              | varchar(100) | YES  |     | NULL           |                |
| user_interests        | varchar(255) | YES  |     | NULL           |                |
| user_biography        | text         | YES  |     | NULL           |                |
| user_actkey           | varchar(32)  | YES  |     | NULL           |                |
| gender                | char(1)      | YES  |     | NULL           |                |
| themes_id             | int(11)      | YES  |     | NULL           |                |
| deleted               | tinyint(1)   | YES  |     | NULL           |                |
| user_viewonline       | tinyint(1)   | YES  |     | 1              |                |
| security_hash         | varchar(32)  | YES  |     | NULL           |                |
| user_karma            | double       | YES  |     | NULL           |                |
| user_authhash         | varchar(32)  | YES  |     | NULL           |                |
+-----------------------+--------------+------+-----+----------------+----------------+
54 rows in set (0.01 sec)
mysql> select * from jforum_users;
Empty set (0.00 sec)

mysql> alter table jforum_users alter user_lang set default 'zh-cn';
Query OK, 0 rows affected (0.53 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table jforum_users alter user_email set default 'default@test.com';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table jforum_users alter user_timezone set default 'GMT+8';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table jforum_users modify user_session_page int NULL default 1;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table jforum_users modify user_posts int NULL default 1;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table jforum_users modify user_timezone varchar(20) NULL default 'GMT+8';
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from jforum_users;
+-----------------------+--------------+------+-----+------------------+----------------+
| Field                 | Type         | Null | Key | Default          | Extra          |
+-----------------------+--------------+------+-----+------------------+----------------+
| user_id               | int(11)      | NO   | PRI | NULL             | auto_increment |
| user_active           | tinyint(1)   | YES  |     | NULL             |                |
| username              | varchar(50)  | NO   |     |                  |                |
| user_password         | varchar(32)  | NO   |     | root             |                |
| user_session_time     | bigint(20)   | YES  |     | 0                |                |
| user_session_page     | int(11)      | YES  |     | 1                |                |
| user_lastvisit        | datetime     | YES  |     | NULL             |                |
| user_regdate          | datetime     | YES  |     | NULL             |                |
| user_level            | tinyint(4)   | YES  |     | NULL             |                |
| user_posts            | int(11)      | YES  |     | 1                |                |
| user_timezone         | varchar(20)  | YES  |     | GMT+8            |                |
| user_style            | tinyint(4)   | YES  |     | NULL             |                |
| user_lang             | varchar(255) | NO   |     | zh-cn            |                |
| user_dateformat       | varchar(20)  | NO   |     | %d/%M/%Y %H:%i   |                |
| user_new_privmsg      | int(11)      | NO   |     | 0                |                |
| user_unread_privmsg   | int(11)      | NO   |     | 0                |                |
| user_last_privmsg     | datetime     | YES  |     | NULL             |                |
| user_emailtime        | datetime     | YES  |     | NULL             |                |
| user_viewemail        | tinyint(1)   | YES  |     | 0                |                |
| user_attachsig        | tinyint(1)   | YES  |     | 1                |                |
| user_allowhtml        | tinyint(1)   | YES  |     | 0                |                |
| user_allowbbcode      | tinyint(1)   | YES  |     | 1                |                |
| user_allowsmilies     | tinyint(1)   | YES  |     | 1                |                |
| user_allowavatar      | tinyint(1)   | YES  |     | 1                |                |
| user_allow_pm         | tinyint(1)   | YES  |     | 1                |                |
| user_allow_viewonline | tinyint(1)   | YES  |     | 1                |                |
| user_notify           | tinyint(1)   | YES  |     | 1                |                |
| user_notify_always    | tinyint(1)   | YES  |     | 0                |                |
| user_notify_text      | tinyint(1)   | YES  |     | 0                |                |
| user_notify_pm        | tinyint(1)   | YES  |     | 1                |                |
| user_popup_pm         | tinyint(1)   | YES  |     | 1                |                |
| rank_id               | int(11)      | YES  |     | 0                |                |
| user_avatar           | varchar(100) | YES  |     | NULL             |                |
| user_avatar_type      | tinyint(4)   | NO   |     | 0                |                |
| user_email            | varchar(255) | NO   |     | default@test.com |                |
| user_icq              | varchar(15)  | YES  |     | NULL             |                |
| user_website          | varchar(255) | YES  |     | NULL             |                |
| user_from             | varchar(100) | YES  |     | NULL             |                |
| user_sig              | text         | YES  |     | NULL             |                |
| user_sig_bbcode_uid   | varchar(10)  | YES  |     | NULL             |                |
| user_aim              | varchar(255) | YES  |     | NULL             |                |
| user_yim              | varchar(255) | YES  |     | NULL             |                |
| user_msnm             | varchar(255) | YES  |     | NULL             |                |
| user_occ              | varchar(100) | YES  |     | NULL             |                |
| user_interests        | varchar(255) | YES  |     | NULL             |                |
| user_biography        | text         | YES  |     | NULL             |                |
| user_actkey           | varchar(32)  | YES  |     | NULL             |                |
| gender                | char(1)      | YES  |     | NULL             |                |
| themes_id             | int(11)      | YES  |     | NULL             |                |
| deleted               | tinyint(1)   | YES  |     | NULL             |                |
| user_viewonline       | tinyint(1)   | YES  |     | 1                |                |
| security_hash         | varchar(32)  | YES  |     | NULL             |                |
| user_karma            | double       | YES  |     | NULL             |                |
| user_authhash         | varchar(32)  | YES  |     | NULL             |                |
+-----------------------+--------------+------+-----+------------------+----------------+
54 rows in set (0.01 sec)

2.3 重新修改了一下代码，终于不报错了，但是0行生效，也就是没有任何数据生效。
在这里插入图片描述

delimiter $$
create procedure in_param3(in p_in int)
BEGIN
			DECLARE username VARCHAR(20);
			DECLARE usermail VARCHAR(20);
			DECLARE i INT DEFAULT 1;
			WHILE i<= 400 DO
			set username = CONCAT('test',LPAD(i,3,'0'));
			set usermail = CONCAT(username,'@test.com');
			INSERT INTO jforum_users VALUES (i,1,username,'root',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'zh-cn','%d/%M/%Y %H:%i',0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,usermail,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
			INSERT INTO jforum_user_groups VALUES (3,i);
			SET i = i +1;
			END WHILE;
END$$
delimiter ;

2.4 实在是不知道怎么搞，然后看到Navicat里除了能在《查询》里执行SQL语句外，还有一个《函数》菜单，直接在函数里面执行成功了，代码如下：
在这里插入图片描述

CREATE DEFINER=`root`@`localhost` PROCEDURE `in_param1`(in p_in int)
BEGIN
			DECLARE username VARCHAR(20);
			DECLARE usermail VARCHAR(20);
			DECLARE i INT DEFAULT 1;
			WHILE i<= 400 DO
			set username = CONCAT('test',LPAD(i,3,'0'));
			set usermail = CONCAT(username,'@test.com');
			INSERT INTO jforum_users VALUES (i,1,username,'root',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'zh-cn','%d/%M/%Y %H:%i',0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,usermail,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
			INSERT INTO jforum_user_groups VALUES (3,i);
			SET i = i +1;
			END WHILE;
END

nonamelake

关注

1
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
You have an error in your SQL syntax问题解决方法

1. 问题现象1.1 最近在学jmeter的分析和优化，需要用mysql准备测试数据，执行如下语句： BEGIN DECLARE username CHAR(20); DECLARE usermail CHAR(20); DECLARE i INT DEFAULT 1; WHILE i<= 400 DO set username = CONCAT('test',LPAD(i,3,'0')); set usermail = CONCAT(username,
复制链接

扫一扫