mysql - SQL:如何从 'tableA pointing to tableB'更新到 'tableA poiting to tableC pointing to tableB'而不丢失现有元素关系？

Question

基本上，我想要一个中间Book，位于从Line到User的直接链接之间。 Line 元素已经存在，并且不能失去与相应已存在的 User 元素的关系，因此需要创建一个默认的 Book 元素来处理已有数据。

表是这样创建的:

User (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
    username VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL
);

Line (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,    
    title VARCHAR(300),            
    id_user INT UNSIGNED NOT NULL, 
    FOREIGN KEY (id_user) REFERENCES User(id)
);

现在我需要创建一些 SQL 代码来生成如下表:

User (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
    username VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL
);

Book (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,    
    title VARCHAR(300),            
    id_user INT UNSIGNED NOT NULL, 
    FOREIGN KEY (id_user) REFERENCES User(id)
);

Line (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,    
    title VARCHAR(300),            
    id_book INT UNSIGNED NOT NULL, 
    FOREIGN KEY (id_book) REFERENCES Book(id)
);

...只不过数据已经存在。所以我有 Line 元素指向 User 元素，并且我无法从头开始创建新表。所以我需要SQL代码来修改现有的结构。

基本上我只是希望用户有书，书有台词，而不是用户有台词。并且已有线路可与所有者用户建立连接。最好的方法是什么？

////////////////////////////////////////////////////////////////////////////////////////////////

一些示例数据代表我的数据库当前的样子:

Table user(id,username)
- 1, user1
- 2, user2
- 3, user3

Table line(id,title,id_user)
- 1, line1, 1
- 2, line2, 1
- 3, linex, 1
- 4, line1, 2
- 5, liney, 2

输出应该是以下数据库:

Table user(id,username)
- 1, user1
- 2, user2
- 3, user3

Table book(id,title,id_user)
- 1, 'default', 1
- 2, 'default', 2
- 3, 'default', 3

Table line(id,title,id_book)
- 1, line1, 1
- 2, line2, 1
- 3, linex, 1
- 4, line1, 2
- 5, liney, 2

/////////////////////////////////

我的管道目前的样子，我的主要问题是生成最终查询:

    create table book
      $sql = "CREATE TABLE IF NOT EXISTS book(
      id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      title VARCHAR(300),
      id_user INT UNSIGNED NOT NULL, 

      FOREIGN KEY (id_user) REFERENCES User(id)
    ); ";

    $conn->exec($sql);


    //insert default book corresponding to every user
    $sql = "INSERT INTO Book(title, id_user) SELECT DISTINCT 'default', id FROM User;";
    $conn->exec($sql);

    //insert id_book foreign key into Line table pointing to Book table
    $sql = "ALTER TABLE Line ADD id_book INT;"

    $conn->exec($sql);

    //make Line.id_book foreign key pointing to Line.id
    $sql="ALTER TABLE Line ADD FOREIGN KEY (id_book) REFERENCES User(id);"

    $conn->exec($sql);

    //unfinished
    $sql = "UPDATE
                Line
            SET
                Line.id_book = Book.id
            WHERE
                Line.id_user = Book.id_user"

Answer 1

创建表book(就像您已经做的那样):

CREATE TABLE IF NOT EXISTS book(
      id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      title VARCHAR(300),
      id_user INT UNSIGNED NOT NULL,     
      FOREIGN KEY (id_user) REFERENCES User(id)
); 

填充新表:

INSERT INTO Book(title, id_user) SELECT 'default', id FROM User;

我从您的代码中删除了 DISTINCT，因为 id 是主要/唯一的，因此每个选定的行都保证是唯一的。

将列 id_book 添加到 line 表中:

ALTER TABLE Line ADD id_book INT UNSIGNED;

它必须是INT UNSIGNED才能匹配引用的数据类型。

添加外键:

ALTER TABLE Line ADD FOREIGN KEY (id_book) REFERENCES book(id); 

它应该引用一本书，而不是像代码中那样的用户。

更新数据(将该行与用户(第一)书相关联):

UPDATE Line
SET Line.id_book = (
  SELECT book.id
  FROM book
  WHERE book.id_user = Line.id_user
  ORDER BY book.id
  LIMIT 1
);

删除line.id_user列:

-- use "SHOW CREATE TABLE line" to find out the foreign key name
ALTER TABLE Line DROP FOREIGN KEY line_ibfk_1;
ALTER TABLE Line DROP id_user;

http://sqlfiddle.com/#!9/3faa13/1

注意:如果用户有两本或更多本书，您不知道要使用 line 链接哪一本书。在您的问题中，您没有定义如何处理这种情况。因此，我决定购买 id 的第一本书(ORDER BY book.id LIMIT 1)。

UPDATE 语句也可以是:

UPDATE Line
SET Line.id_book = (
  SELECT MIN(book.id)
  FROM book
  WHERE book.id_user = Line.id_user
);