kknongchang_2506/app/Module/GameItems/Databases/GenerateSql/item_users.sql

-- 表 kku_item_users 的创建SQL

DROP TABLE IF EXISTS `kku_item_users`;
CREATE TABLE `kku_item_users` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '记录ID，主键',
  `user_id` int NOT NULL COMMENT '用户ID',
  `item_id` int NOT NULL COMMENT '统一属性物品ID，外键关联kku_item_items表',
  `instance_id` int DEFAULT NULL COMMENT '单独属性物品ID，外键关联kku_item_instances表（可为空）',
  `quantity` int NOT NULL DEFAULT '1' COMMENT '数量（对于单独属性物品，该值始终为1）',
  `expire_at` timestamp NULL DEFAULT NULL COMMENT '用户物品过期时间（可为空）',
  `created_at` timestamp NULL DEFAULT NULL COMMENT '获取时间',
  `updated_at` timestamp NULL DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_item` (`user_id`,`item_id`),
  KEY `idx_user_instance` (`user_id`,`instance_id`),
  KEY `idx_expire_at` (`expire_at`),
  KEY `fk_user_item` (`item_id`),
  KEY `fk_user_instance` (`instance_id`),
  CONSTRAINT `fk_user_instance` FOREIGN KEY (`instance_id`) REFERENCES `kku_item_instances` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_user_item` FOREIGN KEY (`item_id`) REFERENCES `kku_item_items` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户物品关联表';