创建表时出现MySQL语法错误

我试图在Filemaker - > MySQL转换脚本中使用此查询。 表创建步骤直接从phpMyAdmin导出中获取，并添加到DROP语句中。

DROP TABLE IF EXISTS artifacts; 

CREATE TABLE IF NOT EXISTS `artifacts` 
  ( 
     `aid`                       INT(11) UNSIGNED NOT NULL auto_increment, 
     `accession number`          TEXT NOT NULL, 
     `name`                      TEXT NOT NULL, 
     `period 1`                  TEXT NOT NULL, 
     `period 3 date`             TEXT NOT NULL, 
     `visual description`        TEXT NOT NULL, 
     `religion 1`                TEXT NOT NULL, 
     `dimen 1 number`            DECIMAL(10, 2) NOT NULL, 
     `dimen 2 number`            DECIMAL(10, 2) NOT NULL, 
     `dimen 3 number`            DECIMAL(10, 2) NOT NULL, 
     `dimen 1 type`              TEXT NOT NULL, 
     `dimen 2 type`              TEXT NOT NULL, 
     `dimen 3 type`              TEXT NOT NULL, 
     `materials 2`               TEXT NOT NULL, 
     `manufacturing processes 2` TEXT NOT NULL, 
     `weight`                    INT(11) NOT NULL, 
     `measuring remarks`         TEXT NOT NULL, 
     `munsell color information` TEXT NOT NULL, 
     `reproduction`              TEXT NOT NULL, 
     `reproduction notes`        TEXT NOT NULL, 
     `published description`     TEXT NOT NULL, 
     `scholarly notes`           TEXT NOT NULL, 
     `bibliography`              TEXT NOT NULL, 
     `comparanda`                TEXT NOT NULL, 
     `exhibit label`             TEXT NOT NULL, 
     `artist`                    TEXT NOT NULL, 
     `spurlock loc 3`            TEXT NOT NULL, 
     `archaeological data`       TEXT NOT NULL, 
     `credit line`               TEXT NOT NULL, 
     `provenance`                TEXT NOT NULL, 
     `museum dedication`         TEXT NOT NULL, 
     `spurlock status`           TEXT NOT NULL, 
     `public description`        TEXT NOT NULL, 
     `working set 5 wb`          TEXT NOT NULL, 
     `image source`              TEXT NOT NULL, 
     `cm mec ma`                 TEXT NOT NULL, 
     `webprivate`                TEXT NOT NULL, 
     `spurlock loc 2`            TEXT NOT NULL, 
     `hiresimagecheck`           TEXT NOT NULL, 
     PRIMARY KEY (`aid`), 
     FULLTEXT KEY `name` (`name`), 
     FULLTEXT KEY `accession number` (`accession number`, `name`, `period 1`, 
     `visual description`, `materials 2`, `published description`, `artist`, 
     `credit line`), 
     FULLTEXT KEY `accession number_2` (`accession number`), 
     FULLTEXT KEY `visual description` (`visual description`), 
     FULLTEXT KEY `published description` (`published description`) 
  ) 
engine=myisam 
DEFAULT charset=latin1 
auto_increment=1; 

DROP TABLE IF EXISTS culture; 

CREATE TABLE IF NOT EXISTS `culture` 
  ( 
     `cid`     INT(10) UNSIGNED NOT NULL auto_increment, 
     `culture` VARCHAR(255) NOT NULL, 
     PRIMARY KEY (`cid`), 
     UNIQUE KEY `culture` (`culture`) 
  ) 
engine=myisam 
DEFAULT charset=latin1 
auto_increment=1; 

DROP TABLE IF EXISTS geocity; 

CREATE TABLE IF NOT EXISTS `geocity` 
  ( 
     `gid`  INT(10) UNSIGNED NOT NULL auto_increment, 
     `city` VARCHAR(255) NOT NULL, 
     PRIMARY KEY (`gid`), 
     UNIQUE KEY `city` (`city`) 
  ) 
engine=myisam 
DEFAULT charset=latin1 
auto_increment=1; 

DROP TABLE IF EXISTS geocontinent; 

CREATE TABLE IF NOT EXISTS `geocontinent` 
  ( 
     `gid`       INT(10) UNSIGNED NOT NULL auto_increment, 
     `continent` VARCHAR(255) NOT NULL, 
     PRIMARY KEY (`gid`), 
     UNIQUE KEY `continent` (`continent`) 
  ) 
engine=myisam 
DEFAULT charset=latin1 
auto_increment=1; 

DROP TABLE IF EXISTS geocountry; 

CREATE TABLE IF NOT EXISTS `geocountry` 
  ( 
     `gid`     INT(10) UNSIGNED NOT NULL auto_increment, 
     `country` VARCHAR(255) NOT NULL, 
     PRIMARY KEY (`gid`), 
     UNIQUE KEY `country` (`country`) 
  ) 
engine=myisam 
DEFAULT charset=latin1 
auto_increment=1; 

DROP TABLE IF EXISTS geolocality; 

CREATE TABLE IF NOT EXISTS `geolocality` 
  ( 
     `gid`      INT(10) UNSIGNED NOT NULL auto_increment, 
     `locality` VARCHAR(255) NOT NULL, 
     PRIMARY KEY (`gid`), 
     UNIQUE KEY `locality` (`locality`) 
  ) 
engine=myisam 
DEFAULT charset=latin1 
auto_increment=1; 

DROP TABLE IF EXISTS georegion; 

CREATE TABLE IF NOT EXISTS `georegion` 
  ( 
     `gid`    INT(10) UNSIGNED NOT NULL auto_increment, 
     `region` VARCHAR(255) NOT NULL, 
     PRIMARY KEY (`gid`), 
     UNIQUE KEY `region` (`region`) 
  ) 
engine=myisam 
DEFAULT charset=latin1 
auto_increment=1; 

DROP TABLE IF EXISTS nomcategory; 

CREATE TABLE IF NOT EXISTS `nomcategory` 
  ( 
     `nid`      INT(10) UNSIGNED NOT NULL auto_increment, 
     `category` VARCHAR(255) NOT NULL, 
     PRIMARY KEY (`nid`), 
     UNIQUE KEY `category` (`category`) 
  ) 
engine=myisam 
DEFAULT charset=latin1 
auto_increment=1; 

DROP TABLE IF EXISTS nomclassification; 

CREATE TABLE IF NOT EXISTS `nomclassification` 
  ( 
     `nid`            INT(10) UNSIGNED NOT NULL auto_increment, 
     `classification` VARCHAR(255) NOT NULL, 
     PRIMARY KEY (`nid`), 
     UNIQUE KEY `classification` (`classification`) 
  ) 
engine=myisam 
DEFAULT charset=latin1 
auto_increment=1; 

DROP TABLE IF EXISTS nomsubclassification; 

CREATE TABLE IF NOT EXISTS `nomsubclassification` 
  ( 
     `nid`               INT(10) UNSIGNED NOT NULL auto_increment, 
     `subclassification` VARCHAR(255) NOT NULL, 
     PRIMARY KEY (`nid`), 
     UNIQUE KEY `subclassification` (`subclassification`) 
  ) 
engine=myisam 
DEFAULT charset=latin1 
auto_increment=1; 

但是，当我尝试执行此查询时，我得到：

MySQL Error: 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 TABLE IF NOT EXISTS `artifacts` 
  ( 
     `aid`                       IN' at line 3

语法对我来说都很好，任何人都可以发现错误？ 我使用的唯一SQL格式化程序报告没有错误。

你的脚本工作正常。 问题在于你执行它的方式（客户端期望通过查询而不是整个脚本来查询），或者你的分隔符改为别的;

DELIMITER ;

USE databasename;

CREATE TABLE tablename (
name_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
 (etc....)
);

等等

上一篇: MySQL Syntax Error on Table Creation

下一篇: mysql utf8 character problem