MySQL数据库中创建商品信息表的SQL脚本

由 ChatGPT4o 使用 ChatGPT 4o 回答

问题：

/* Navicat Premium Data Transfer

Source Server : 本地连接 Source Server Type : MySQL Source Server Version : 50723 Source Host : localhost:3306 Source Schema : shop-second

Target Server Type : MySQL Target Server Version : 50723 File Encoding : 65001

Date: 05/03/2024 20:07:03 */

SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0;

-- Table structure for goods

DROP TABLE IF EXISTS goods; CREATE TABLE goods ( goods_id int(11) NOT NULL AUTO_INCREMENT, user_id int(11) NULL DEFAULT NULL COMMENT '发布人id', category_id int(11) NULL DEFAULT NULL COMMENT '分类id', type varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '0:闲置 1：求购', goods_name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '商品名称', goods_desc varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '简介', goods_price de

回答：
cimal(10, 2) NULL DEFAULT NULL COMMENT '价格',
  `goods_image` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '图片',
  `goods_status` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '0:在售 1：下架',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`goods_id`) USING BTREE,
  INDEX `user_id`(`user_id`) USING BTREE,
  INDEX `category_id`(`category_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

This SQL script creates a table named goods in a MySQL database. The table is designed to store information about goods, including details such as the user who posted the item, the category of the item, its type (either "闲置" for idle or "求购" for wanted), name, description, price, image, status (either "在售" for on sale or "下架" for off the shelf), and timestamps for creation and last update.

Here is a breakdown of the table structure:

  • goods_id: An auto-incrementing primary key for the goods.
  • user_id: An integer representing the ID of the user who posted the item.
  • category_id: An integer representing the category ID of the item.
  • type: A varchar field indicating the type of the item (0 for idle, 1 for wanted).
  • goods_name: A varchar field for the name of the item.
  • goods_desc: A varchar field for a brief description of the item.
  • goods_price: A decimal field for the price of the item.
  • goods_image: A varchar field for the image URL of the item.
  • goods_status: A varchar field indicating the status of the item (0 for on sale, 1 for off the shelf).
  • create_time: A timestamp field for the creation time of the record, defaulting to the current timestamp.
  • update_time: A timestamp field for the last update time of the record, automatically updating to the current timestamp on modification.

Indexes are created on user_id and category_id to optimize queries involving these columns. The table uses the InnoDB storage engine with UTF-8 encoding.