MySQL #1064语法错误求助：XAMPP环境建表语句报错

我帮你找到了两处语法错误的根源，都是逗号使用不当导致的——这是编写SQL时非常容易疏忽的小细节，咱们来逐个排查：

错误1：ACTORS表的ENGINE行语法错误 #

在ACTORS表的定义中，PRIMARY KEY (actor_id)语句末尾多了一个额外的逗号，导致MySQL无法正确解析后续的ENGINE=InnoDB配置：

-- 错误写法
PRIMARY KEY (actor_id),
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

这里的多余逗号会让MySQL误以为还有字段要定义，从而在遇到)ENGINE时触发语法错误。

错误2：FILESTAG表的PRIMARY KEY行语法错误 #

在FILESTAG表的定义中，PRIMARY KEY (files_tag_id)语句末尾缺少了必要的逗号，导致后续的KEY idx_fk_tag_id无法被正确识别：

-- 错误写法
PRIMARY KEY (files_tag_id)
KEY idx_fk_tag_id (tag_id),

缺少逗号会让MySQL把PRIMARY KEY和后面的KEY语句连在一起解析，不符合SQL的语法规范。

修正后的完整SQL语句 #

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

DROP SCHEMA IF EXISTS htmlProject;
CREATE SCHEMA htmlProject;
USE htmlProject;

-- Table structure for table `actor`
CREATE TABLE ACTORS (
    actor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    actor_name VARCHAR(50) NOT NULL,
    joined_on DATETIME NOT NULL,
    actor_password VARCHAR(50) NOT NULL,
    PRIMARY KEY (actor_id)  -- 移除了多余的逗号
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE TAGS (
    tag_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
    tag_name VARCHAR(25) NOT NULL,
    PRIMARY KEY (tag_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE FILES (
    files_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    files_name VARCHAR(45) NOT NULL,
    uploaded_on DATETIME NOT NULL,
    PRIMARY KEY (files_id),
    actor_id SMALLINT UNSIGNED NOT NULL,
    KEY idx_fk_actor_id (actor_id),
    CONSTRAINT `fk_files_actors` FOREIGN KEY (actor_id) REFERENCES ACTORS(actor_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE FILESTAG (
    files_tag_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    tag_id SMALLINT UNSIGNED NOT NULL,
    files_id SMALLINT UNSIGNED NOT NULL,
    PRIMARY KEY (files_tag_id),  -- 添加了缺失的逗号
    KEY idx_fk_tag_id (tag_id),
    KEY idx_fk_files_id (files_id),
    CONSTRAINT `fk_files_tags` FOREIGN KEY (tag_id) REFERENCES TAGS (tag_id) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT `fk_tags_files` FOREIGN KEY (files_id) REFERENCES FILES (files_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

内容的提问来源于stack exchange，提问作者Hany Daher