mysql - 对在mysql中创建表感到困惑

Question

我在一年多以前在stackoverflow上发现了一个问题，这个问题和我在mysql中的数据库结构很相似。问题找到here

除了下表之外，已提供的答案是有道理的:

Message_type: message_type_id, description (1:public, 2:friends, 3:specific_recipients)

有人能帮我弄清楚如何创建实现 (1:public, 2:friends, 3:specific_recipients) 属性的表吗？

编辑

User: user_id, given_name, ...

Message: message_id, owner_id (fk User), subject, body, posted, message_type_id (fk Message_type)...

Message_recipients: user_id (fk User), message_id (fk Message)

Message_type: message_type_id, description (1:public, 2:friends, 3:specific_recipients)

所以我尝试基于此实现我的数据库，但我在设置它时遇到了问题。第一个问题:message 表有一个名为 message_type_id 的 FK 但如果您查看上一个 OP 的问题，该字段称为 isPublic 并且那不是 PK。如何在不删除其记录的情况下将 isPublic 字段更改为 FK。在创建这些表时，我还需要指定任何 UPDATES 或 ALTER 条件吗？

Answer 1

在我看来，Message_type 有两列 message_type_id 和 description 并且包含 3 行，括号中包含值，如下所示:

message_type_id | description
----------------+--------------------
1               | public
2               | friends
3               | specific_recipients

有几种方法可以编写 SQL 来执行此操作，一种方法(使用 message_type_id 作为主键):

CREATE TABLE Message_type (message_type_id INT NOT NULL PRIMARY KEY,
                           description VARCHAR(100))
SELECT 1 AS message_type_id, 'public' AS description
UNION ALL
SELECT 2, 'friends'
UNION ALL
SELECT 3, 'specific_recipients'

参见 this了解更多。

或者:

CREATE TABLE Message_type (message_type_id INT NOT NULL PRIMARY KEY,
                           description VARCHAR(100));

INSERT INTO message_type
VALUES (1, 'public'), (2, 'friends'), (3, 'specific_recipients')

编辑问题编辑:

如果您已经创建了 Message 表(例如):

CREATE TABLE Message (
  message_id int(10) unsigned NOT NULL auto_increment,
  isPublic tinyint(4) default '0',
  PRIMARY KEY (message_id)
);

这将用 message_type_id 替换(可能是 bool 值)isPublic:

ALTER TABLE Message
ADD COLUMN message_type_id INT REFERENCES Message_type(message_type_id);

# classified everything that's not public as friends
UPDATE Message
SET message_type_id = CASE WHEN isPublic = 1 THEN 1
                           ELSE 2
                      END;

ALTER TABLE Message DROP COLUMN isPublic;