admin 管理员组文章数量: 1087131
You have an error in your SQL syntax问题解决方法
1. 问题现象
1.1 最近在学jmeter的分析和优化，需要用mysql准备测试数据，执行如下语句：
BEGINDECLARE username CHAR(20);DECLARE usermail CHAR(20);DECLARE i INT DEFAULT 1;WHILE i<= 400 DOset username = CONCAT('test',LPAD(i,3,'0'));set usermail = CONCAT(username,'@test');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 在网上找了好久也没有找到一个有用的案例，没办法，找不到案例就从头学一下吧，在如下网站看了一下.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: 0mysql> alter table jforum_users alter user_email set default 'default@test';
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> alter table jforum_users alter user_timezone set default 'GMT+8';
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> 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: 0mysql> alter table jforum_users modify user_posts int NULL default 1;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> 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: 0mysql> 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 | |
| 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)
BEGINDECLARE username VARCHAR(20);DECLARE usermail VARCHAR(20);DECLARE i INT DEFAULT 1;WHILE i<= 400 DOset username = CONCAT('test',LPAD(i,3,'0'));set usermail = CONCAT(username,'@test');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)
BEGINDECLARE username VARCHAR(20);DECLARE usermail VARCHAR(20);DECLARE i INT DEFAULT 1;WHILE i<= 400 DOset username = CONCAT('test',LPAD(i,3,'0'));set usermail = CONCAT(username,'@test');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
本文标签： You have an error in your SQL syntax问题解决方法
版权声明：本文标题：You have an error in your SQL syntax问题解决方法 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.roclinux.cn/b/1693750881a240668.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论