今天在使用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