#1064 - 您的 SQL 语法有错误；检查与您的 MariaDB 服务器相对应的手册 [英] #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server

问题描述

我试图在我的 sql 服务器上运行以下查询:

I was trying to run following Query on my sql server :

CREATE TABLE `e_store`.`products`(
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
    `name` VARCHAR(250) NOT NULL ,
    `brand_id` INT UNSIGNED NOT NULL ,
    `category_id` INT UNSIGNED NOT NULL ,
    `attributes` JSON NOT NULL ,
    PRIMARY KEY(`id`) ,
    INDEX `CATEGORY_ID`(`category_id` ASC) ,
    INDEX `BRAND_ID`(`brand_id` ASC) ,
    CONSTRAINT `brand_id` FOREIGN KEY(`brand_id`) REFERENCES `e_store`.`brands`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE ,
    CONSTRAINT `category_id` FOREIGN KEY(`category_id`) REFERENCES `e_store`.`categories`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE
);

我的 e_store 数据库中已有品牌和类别表.

I have already brands and categories tables on my e_store database.

但我收到以下错误:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'JSON NOT NULL ,
    PRIMARY KEY(`id`) ,
    INDEX `CATEGORY_ID`('category_id' ' at line 6

推荐答案

对于和我一样面临这个问题的人:

For those who are facing this issue similar to me:

MariaDB 本身并未实现 JSON 数据类型，但出于兼容性原因，它使用它作为 LONGTEXT 的别名.根据文档(https://mariadb.com/kb/en/库/json-data-type/):

MariaDB does not natively implement the JSON data type but it uses it as an alias for LONGTEXT for compatibility reasons. According to the documentation (https://mariadb.com/kb/en/library/json-data-type/):

JSON 是为了与 MySQL 的 JSON 数据类型兼容而引入的 LONGTEXT 的别名.MariaDB 将其实现为 LONGTEXT，因为 JSON 数据类型与 SQL 标准相矛盾，而 MariaDB 的基准测试表明性能至少相当.

JSON is an alias for LONGTEXT introduced for compatibility reasons with MySQL's JSON data type. MariaDB implements this as a LONGTEXT rather, as the JSON data type contradicts the SQL standard, and MariaDB's benchmarks indicate that performance is at least equivalent.

为了确保插入一个有效的 json 文档，可以使用 JSON_VALID 函数作为 CHECK 约束.

In order to ensure that a a valid json document is inserted, the JSON_VALID function can be used as a CHECK constraint.

因此，如果您对 MariaDB 中的 JSON 数据类型有疑问，只需更改为 LONGTEXT.;-)

So if you are having issues with the JSON data type in MariaDB, simply just change to LONGTEXT. ;-)

这篇关于#1064 - 您的 SQL 语法有错误；检查与您的 MariaDB 服务器相对应的手册的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！