基于Node.js/Express/Angular的用户自定义变量MySQL存储方案咨询

一、JSON列是不是最优选择？ #

对于你要做的Twine类互动故事，用户自定义变量的结构和数量都是动态的，JSON列确实是很合适的方案。对比其他方式：

• 要是用键值对单独建表，变量多了之后查询得频繁关联，性能会掉，维护起来也麻烦；
• MySQL的动态列功能灵活性远不如JSON，开发成本还高。
  而且MySQL 5.7及以上版本支持JSON虚拟列索引，能解决后续查询的性能问题，所以这个场景下JSON列是最优选择之一。

二、JSON列的安全实现要点 #

• 输入要先校验：
  • 前端Angular做表单验证，限制变量名只能用字母、数字、下划线，别让用户瞎输奇怪的内容；
  • 后端Express用joi或者zod这类库验证JSON结构，防止恶意的嵌套过深、超大字段数据进来。
• 防SQL注入：
  • 用参数化查询，比如mysql2的预编译语句，绝对别直接把JSON字符串拼到SQL里。示例代码：

  const [result] = await connection.execute(
    'UPDATE stories SET variables = ? WHERE id = ?',
    [JSON.stringify(userVariables), storyId]
  );
  

• 过滤敏感内容：
  • 存储前把脚本标签、特殊字符这类可能导致XSS的内容过滤掉，虽然JSON本身不会执行，但前端渲染的时候还是要小心。
• 权限卡严：
  • 必须校验用户只能修改自己创建的故事变量，后端要核对用户ID和故事的归属关系，别让别人乱改。

三、大量数据存储的优化技巧 #

• 加合适的索引：
  • 对JSON里常用的查询字段创建虚拟列索引，比如经常查user_name变量的话：

  ALTER TABLE stories ADD COLUMN var_user_name VARCHAR(255) GENERATED ALWAYS AS (variables->>'$.user_name') STORED;
  CREATE INDEX idx_var_user_name ON stories(var_user_name);
  

• 拆分过大的数据：
  • 如果单条JSON超过1MB，考虑把变量拆成多个JSON列，或者把长文本、大数组这类大型变量单独存到另一张表，关联故事ID。
• 用缓存减压：
  • 用Redis缓存热门故事的变量数据，减少数据库的查询压力，毕竟互动故事可能有很多用户频繁读。
• 分批处理请求：
  • 用户提交大量变量时，前端分批发请求，后端分批写入，避免一次性处理太大的JSON导致内存溢出。

四、适配Twine类互动故事的额外建议 #

• 加版本控制：
  • 给变量加版本号，或者存历史快照，让用户能回滚到之前的变量状态，这对故事创作来说很实用。
• 支持多种变量类型：
  • 除了字符串、数字，还要支持布尔、数组、对象，满足复杂的故事逻辑，JSON本身就能保留这些类型信息；前端可以做可视化的类型选择，让用户编辑起来更方便。
• 提前做性能测试：
  • 模拟大量用户创建大量变量的场景，测测数据库的读写性能，必要时调整MySQL的配置参数，比如innodb_log_file_size。

内容的提问来源于stack exchange，提问作者Alexandru Ungureanu