在MySQL中，根据另一个表中的新条目添加一个表中的新列

Question

我有两个表：sessions和assignments。这个assignments表有一个叫做 scriptname 的列，其值为字符串。sessions表的列名与scriptname相同，同时还有id、uid、timein和timeout这些列。当我向assignments添加新实例时，会在sessions中添加新的列，列名为scriptname，并且默认值为0。我应该如何做呢？

目前我所做的是删除这个表，并基于scriptname列创建一个新表。问题是，我失去了所有数据。

DROP TABLE sessions;
SET SESSION group_concat_max_len = 1000000;
SELECT
  CONCAT(
    'CREATE TABLE sessions (',
    GROUP_CONCAT(DISTINCT
      CONCAT(scriptname, ' BOOL DEFAULT 0')
      SEPARATOR ','),
    ');')
FROM
  assignments
INTO @sql;

PREPARE stmt FROM @sql;
EXECUTE stmt;

ALTER TABLE sessions
ADD COLUMN `timeout` timestamp not null FIRST,
ADD COLUMN `timein` timestamp not null DEFAULT CURRENT_TIMESTAMP FIRST,
ADD COLUMN `uid` VARCHAR(128) not null FIRST,
ADD COLUMN `id` INT(11) AUTO_INCREMENT PRIMARY KEY not null FIRST;

我希望有人能够帮助我，因为我对SQL并不是很熟悉！提前感谢。

- Ansjovis86

---

如果您经常向表中添加和删除列，则您的设计是错误的。动态信息应该在列值中，而不是列名中。 - Barmar

你能否更加详细地解释一下？能否在脚本名称中给出值，并提供所需的输出会话表？ - MohanaPriyan

Answer 1

考虑将行添加到表格中，稍后在显示输出时“旋转”以创建列。添加列非常笨拙，正如你所发现的那样。添加太多列最终会达到某个限制。添加行基本上是无限的。此论坛中已经多次讨论过旋转，因此我在这里不再重复了。

- Rick James

Answer 2

这里的数据模型比较弱，不适合关系型数据库（RDBMS）——在这种情况下，你需要通过关联来强制执行数据集上的限制。

例如，在您的情况下，您可以通过将“scriptname”作为外键添加到“sessions”表中来实现。因此，对于您认为是一个类型的记录，您将在“sessions”表中拥有多条记录，每条记录包含1个“scriptname”值（代表模型中的列名）和相关值（表中的字段值）。然后，您可以在客户端将这两列“旋转”并组装成一行。

否则，如果您坚持以任何代价保留这种非规范化、非刚性的数据结构，最好选择一些面向列/多模型的数据库，例如Apache Cassandra。在那里，您可以随时更改列族/表结构，而无需停止/删除/重新创建/运行。

- Yuri G

Answer 3

你需要以某种方式了解新添加的列的列表。例如，你可能有一个名为 date_added 的列，在其中记录了新会话添加的时间。

如果你没有这样的列，则可以从 INFORMATION_SCHEMA.COLUMNS 获取列的列表。然后，你所需要做的就是创建一个类似于这样的 ALTER TABLE 脚本（这将向表中添加4月份添加的新会话的新列）：

SET SESSION group_concat_max_len = 1000000;
SELECT
  CONCAT(
    'ALTER TABLE sessions ',
    GROUP_CONCAT(DISTINCT
      CONCAT('ADD COLUMN ', scriptname, ' BOOL DEFAULT 0')
      SEPARATOR ','),
    ';')
FROM
  assignments
WHERE scriptname NOT IN (
  SELECT COLUMN_NAME
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'sessions'
  AND table_schema = '<your schema>')
INTO @sql;

PREPARE stmt FROM @sql;
EXECUTE stmt;

-- this should generate this:
-- ALTER TABLE sessions 
--  ADD COLUMN blah1 BOOL DEFAULT 0, 
--  ADD COLUMN blah2 BOOL DEFAULT 0;

然而，我必须指出您需要重新设计数据的存储方式。您当前的存储方式不是最佳的。您需要按行存储会话，而不像您现在这样按列存储。如果您需要以这种方式呈现数据，则可以构建查询来透视数据。

- cha

---

目前，会话是存储在行中的，但我想描述更多有关会话的特性，这需要添加新的列。 - Ansjovis86

你可以使用 INFORMATION_SCHEMA.COLUMNS 来查找已添加列的列表，根据我的更新答案。 - cha

好的，我会尝试你的解决方案，并在结果出来后再与你联系。 - Ansjovis86

还有些问题： #1054 - 在'where clause'中未知列'sessions' - Ansjovis86

我认为你的@sql变量不够大，无法容纳所有的SQL语句。请将其大小调大，例如varchar（2000）。 - cha

Answer 4

正如其他人所说，动态生成一个不断增长的列列表显然不是你应该做的。SQL就是不为此而设计的。

如果是我，基于我对你如何使用这个功能的有限了解，我会创建第三个表（有两列：“脚本名称”和“值”），并将其与会话表连接。这样，您就可以在每个会话中拥有任意数量的赋值（这似乎是您的目标）。

- A Hettinger

Answer 5

您可以在另一个表中插入新条目时设置触发器。例如 --

CREATE TRIGGER `ADD_COLUMN_BEFORE_INSERT` BEFORE INSERT ON `table_1`
FOR EACH ROW
 BEGIN
   ALTER TABLE table_2........;
 END

- Zigri2612

---

谢谢你的回答。那在我的例子中会是什么样子？ - Ansjovis86