今天在使用mysql(版本是5.0.21)插入数据时,总是遇到莫名其妙的1064错误.过程如下:
//先显示表的结构看看.//插入一条记录,出错了.你能看到哪儿错了么?
mysql
>
desc
position
-> ;
+ -- ------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+ -- ------------+--------------+------+-----+---------+----------------+
| id | bigint ( 19 ) | NO | PRI | NULL | auto_increment |
| departmentId | bigint ( 19 ) | NO | MUL | NULL | |
| name | varchar ( 200 ) | NO | | NULL | |
| description | varchar ( 200 ) | YES | | NULL | |
| ldapObjGUID | varchar ( 50 ) | YES | | NULL | |
| privilege | int ( 10 ) | YES | | NULL | |
+ -- ------------+--------------+------+-----+---------+----------------+
6 rows in set ( 0.01 sec)
-> ;
+ -- ------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+ -- ------------+--------------+------+-----+---------+----------------+
| id | bigint ( 19 ) | NO | PRI | NULL | auto_increment |
| departmentId | bigint ( 19 ) | NO | MUL | NULL | |
| name | varchar ( 200 ) | NO | | NULL | |
| description | varchar ( 200 ) | YES | | NULL | |
| ldapObjGUID | varchar ( 50 ) | YES | | NULL | |
| privilege | int ( 10 ) | YES | | NULL | |
+ -- ------------+--------------+------+-----+---------+----------------+
6 rows in set ( 0.01 sec)
mysql
>
insert
into
position(id,departmentId,name,description,ldapObjGUID,privilege)
-> values ( 9 , 3 , ' ere ' , ' a123 ' , ' e123 ' , 1 );
ERROR 1064 ( 42000 ): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right synta
x to use near ' position(id,departmentId,name,description,ldapObjGUID,privilege)
values(9,3, ' er ' at line 1
-> values ( 9 , 3 , ' ere ' , ' a123 ' , ' e123 ' , 1 );
ERROR 1064 ( 42000 ): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right synta
x to use near ' position(id,departmentId,name,description,ldapObjGUID,privilege)
values(9,3, ' er ' at line 1
//下面这一条可以插入了.和上面一条的区别是什么?
mysql
>
insert
into
position (id,departmentId,name,description,ldapObjGUID,privilege)
values
(
9
,
3
,
'
test
'
,
'
a123
'
,
'
e123
'
,
1
);
Query OK, 1 row affected ( 0.03 sec)
Query OK, 1 row affected ( 0.03 sec)
原因很简单,第一次插入的sql语句 position和 ( 之前没有空格,mysql执行时无法解析,所以报1064语法错误.
晕倒了,垃圾软件见多了,没有见到这么垃圾的.
继续实验,又发现其它表这样插入又是可以(以是表名和'('之间可以没有空格.下面给出该表创建脚本,大家看看啊.
--
----------------------------------------------------------------------
-- MySQL Migration Toolkit
-- SQL Create Script
-- ----------------------------------------------------------------------
SET FOREIGN_KEY_CHECKS = 0 ;
CREATE DATABASE IF NOT EXISTS `db1`;
USE `db1`;
-- -------------------------------------
-- Tables
DROP TABLE IF EXISTS `db1`.`Department`;
CREATE TABLE `db1`.`Department` (
`id` BIGINT ( 19 ) NOT NULL primary key ,
`name` VARCHAR ( 100 ) NOT NULL
)
ENGINE = INNODB;
DROP TABLE IF EXISTS `db1`.`Position`;
CREATE TABLE `db1`.`Position` (
`id` BIGINT ( 19 ) NOT NULL AUTO_INCREMENT,
`departmentId` BIGINT ( 19 ) NOT NULL ,
`name` VARCHAR ( 200 ) NOT NULL ,
`description` VARCHAR ( 200 ) NULL ,
`ldapObjGUID` VARCHAR ( 50 ) NULL ,
`privilege` INT ( 10 ) NULL ,
PRIMARY KEY (`id`),
INDEX `IX_Position` (`id`),
UNIQUE INDEX `IX_Position_1` (`departmentId`, `name`),
CONSTRAINT `FK_Position_Department` FOREIGN KEY `FK_Position_Department` (`departmentId`)
REFERENCES `DrmPolicy20`.`Department` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION
)
ENGINE = INNODB;
SET FOREIGN_KEY_CHECKS = 1 ;
insert into department values ( 2 , ' testdept1 ' )
-- 下面这条会出错的.
insert into position(id,departmentId,name) values ( 1 , 2 , ' pos1 ' )
-- 下面这条才能执行.
insert into position (id,departmentId,name) values ( 1 , 2 , ' pos1 ' )
-- ----------------------------------------------------------------------
-- EOF
-- MySQL Migration Toolkit
-- SQL Create Script
-- ----------------------------------------------------------------------
SET FOREIGN_KEY_CHECKS = 0 ;
CREATE DATABASE IF NOT EXISTS `db1`;
USE `db1`;
-- -------------------------------------
-- Tables
DROP TABLE IF EXISTS `db1`.`Department`;
CREATE TABLE `db1`.`Department` (
`id` BIGINT ( 19 ) NOT NULL primary key ,
`name` VARCHAR ( 100 ) NOT NULL
)
ENGINE = INNODB;
DROP TABLE IF EXISTS `db1`.`Position`;
CREATE TABLE `db1`.`Position` (
`id` BIGINT ( 19 ) NOT NULL AUTO_INCREMENT,
`departmentId` BIGINT ( 19 ) NOT NULL ,
`name` VARCHAR ( 200 ) NOT NULL ,
`description` VARCHAR ( 200 ) NULL ,
`ldapObjGUID` VARCHAR ( 50 ) NULL ,
`privilege` INT ( 10 ) NULL ,
PRIMARY KEY (`id`),
INDEX `IX_Position` (`id`),
UNIQUE INDEX `IX_Position_1` (`departmentId`, `name`),
CONSTRAINT `FK_Position_Department` FOREIGN KEY `FK_Position_Department` (`departmentId`)
REFERENCES `DrmPolicy20`.`Department` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION
)
ENGINE = INNODB;
SET FOREIGN_KEY_CHECKS = 1 ;
insert into department values ( 2 , ' testdept1 ' )
-- 下面这条会出错的.
insert into position(id,departmentId,name) values ( 1 , 2 , ' pos1 ' )
-- 下面这条才能执行.
insert into position (id,departmentId,name) values ( 1 , 2 , ' pos1 ' )
-- ----------------------------------------------------------------------
-- EOF