mysql - 使文本列不为空的正确方法是什么

Question

我正在尝试在 mysql 中创建一个表

CREATE TABLE messages (
message_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
time_stamp DATETIME NOT NULL,
from INT UNSIGNED NOT NULL,
to INT UNSIGNED NOT NULL,
message_content TEXT NOT NULL,
media_id INT,
seen BOOLEAN,
subject TINYTEXT NOT NULL,
PRIMARY KEY (message_id),
FOREIGN KEY (from) REFERENCES user (user_id),
FOREIGN KEY (to) REFERENCES user (user_id),
FOREIGN KEY (media_id) REFERENCES digital_media (media_id),
INDEX (time_stamp, from, to, seen));

但我收到错误消息

ERROR 1064 (42000): 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 'from INT UNSIGNED NOT NULL,
to INT UNSIGNED NOT NULL,
message_content TEXT NOT N' at line 4

我不知道该错误消息是什么意思，我不明白为什么语法是错误的。

Answer 1

to和 from列的名称很糟糕，因为它们是 SQL 关键字。

我认为这会奏效:

CREATE TABLE messages (
    message_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    time_stamp DATETIME NOT NULL,
    from_user_id INT UNSIGNED NOT NULL,
    to_user_id INT UNSIGNED NOT NULL,
    message_content TEXT NOT NULL,
    media_id INT,
    seen BOOLEAN,
    subject TINYTEXT NOT NULL,
    PRIMARY KEY (message_id),
    FOREIGN KEY (from_user) REFERENCES user (user_id),
    FOREIGN KEY (to_user) REFERENCES user (user_id),
    FOREIGN KEY (media_id) REFERENCES digital_media (media_id),
    INDEX (time_stamp, from_user, to_user, seen)
);

It works没有外键约束。