MySQL/MariaDB 不接受 JSON 格式?无法创建数据库

2022-01-15 00:00:00 mariadb php mysql

我目前在 phpmyadmin 上使用 XAMPP/Apache 和 MariaDB.我正在尝试根据我的代码使用 Doctrine 创建一个表,因此使用注释来验证表单.我只是想将表单中输入的值存储在数据库中.在另一个示例中,这非常有效.

I am currently using XAMPP/Apache with MariaDB on phpmyadmin. I am trying to create a table based on my code using Doctrine and therefore Annotations for validating a form. I simply want to store the entered values from the form in the database. In another example this worked perfectly fine.

但现在我有一个复选框字段",我猜它在创建数据库时会造成某种麻烦.

But now i am having a "Checkbox field" that i guess is causing some kind of trouble when creating the database.

我在控制台中使用这些命令:

I am using those commands in the console:

php bin/console make:migration

之后:

php bin/console doctrine:migrations:migrate

当调用第二个 = 当我尝试在我的数据库中创建表时,我收到以下错误:

When calling the 2nd one = when i try to create the table in my database i get the following errors:

Migration 20181121103017 failed during Execution. 
Error An exception occurred while executing 'CREATE TABLE pizza (id INT 
AUTO_INCREMENT NOT NULL, name VARCHAR(255) NOT NULL, address VARCHAR(255) NOT NULL
phone VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL, 
size INT NOT NULL, ingredient JSON NOT NULL COMMENT '(DC2Type:json_array)', delivery INT NOT NULL, 
PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB':


SQLSTATE[42000]: Syntax error or access violation: 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 COMMENT '(DC2Type:json_
array)', delivery INT NOT NULL, PRIMARY KEY' at line 1

此时我真的不知道自己做错了什么.

I am really not knowing what i am doing wrong at this point.

这些是我在Pizza.php"文件中的注释.

These are my annotations i have in my "Pizza.php" File.

// ------------------

/**
 * @AssertNotBlank(
 *     message = "E-Mail Address required"
 * )
 * @AssertEmail(
 *     message = "The email '{{ value }}' is not a valid email."
 * )
 * @AssertLength(min="2", max="255")
 * @ORMColumn(type="string", length=255)
 */

protected $email;

// ------------------

/**
 * @AssertRange(min=0, max=3)
 * @ORMColumn(type="integer")
 */

protected $size;

// ------------------

/**
 *@AssertNotBlank(
 *     message = "Choose at least 1 ingredient!"
 * )
 * @ORMColumn(type="array")
 */

protected $ingredient;

// ------------------

/**
 * @AssertNotBlank(
 *     message = "Choose a delivery option!"
 * )
 * @AssertRange(min=0, max=1)
 * @ORMColumn(type="integer")
 */

protected $delivery;

表单本身运行良好,验证按我的预期工作.

The form itself is working perfectly fine, the validations do work the way i intended.

我到底做错了什么?

如果您需要我的Pizza.php"(实体类)或我在其中进行路由的控制器文件中的更多代码,请告诉我.

If you need any more code from my "Pizza.php" (Entity Class) or my Controller File where i did my routing please let me know.

感谢您的帮助!

推荐答案

根据您的评论,Doctrine 似乎认为它可以使用您的 mariadb 版本上不可用的功能.

Based on your comments, it seems that Doctrine thinks it can use features that are not available on your version of mariadb.

如果您告诉教义您使用的是哪个版本,它将为该列选择正确的数据类型,在这种情况下可能是 LONGTEXT 或类似的东西.

If you tell doctrine which version you are using, it will select the correct datatype for that column, in this case probably LONGTEXT or something similar.

根据您使用的内容,它看起来像(例如在 symfony 中使用 yaml 文件):

Depending on what you are using, it would look something like (using a yaml file in symfony for example):

doctrine:
    dbal:
        server_version: '10.1'

请注意,您需要重新生成迁移.

Note that you would need to re-generate your migrations.

就像我在评论中提到的那样,我个人会规范化数据库并使用不同的表将比萨饼与配料相关联,以便更轻松地搜索和过滤.

Like I mentioned in my comment, personally I would normalize the database and use a different table to link the pizza's to the ingredients to make searching and filtering easier.

相关文章