创建表时出现MySQL语法错误 [英] MySQL Syntax Error on Table Creation

问题描述

我正在尝试在Filemaker-> MySQL过渡脚本中使用此查询.表创建步骤直接来自phpMyAdmin导出，并且在DROP语句中添加了该表.

I am trying to use this query in a Filemaker -> MySQL transition script. The table creation steps are taken directly from a phpMyAdmin export, and I added in the DROP statements.

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; 

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

However, when I attempt to execute this query, I get:

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格式化程序不会报告任何错误.

The syntax all looks good to me, can anyone spot the error? The only SQL formatter that I use reports no errors.

推荐答案

您的脚本可以正常工作.问题可能是执行它的方式(客户端将期望通过查询而不是整个脚本来查询)，或者您已将分隔符更改为;

Your script works fine. The problem is either the way you are executing it (client will expect query by query not the whole script) or you have changed your delimiter to something else than ;

DELIMITER ;

这篇关于创建表时出现MySQL语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！