修改Mysql 添加字段和增加索引_mysql添加字段索引alert table-优快云博客

本文链接：https://blog.youkuaiyun.com/wangqiaowq/article/details/142353485

CREATE TABLE `user_session` (
  `session_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '当前会话的id',
  `user_id` varchar(36) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '当前会话所属的用户的id',
  `session_name` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '当前会话的名字',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `type` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '聊天的类型，区分普通聊天0、pdf聊天1、冒险游戏聊天2、专家领域3',
  PRIMARY KEY (`session_id`) USING BTREE,
  KEY `idx_user_id` (`user_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5610 DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC

ALTER TABLE user_session ADD isDel TINYINT(1) NOT NULL DEFAULT '0' COMMENT '标识记录是否已删除，0-未删除，1-已删除';

ALTER TABLE user_session ADD INDEX idx_user_id_isDel (user_id, isDel);

修改后

CREATE TABLE `user_session` (
    `session_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '当前会话的id',
    `user_id` varchar(36) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '当前会话所属的用户的id',
    `session_name` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '当前会话的名字',
    `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
    `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `type` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '聊天的类型，区分普通聊天0、pdf聊天1、冒险游戏聊天2、专家领域3',
    `isDel` tinyint(1) NOT NULL DEFAULT '0' COMMENT '标识记录是否已删除，0-未删除，1-已删除',
    PRIMARY KEY (`session_id`) USING BTREE,
    KEY `idx_user_id` (`user_id`) USING BTREE,
    KEY `idx_user_id_isDel` (`user_id`,`isDel`)
) ENGINE = InnoDB AUTO_INCREMENT = 711 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

修改字段名

MySQL [business_chatgpt]> ALTER TABLE user_session CHANGE isDel is_del TINYINT(1) NOT NULL DEFAULT '0' COMMENT '标识记录是否已删除，0-未删除，1-已删除';
Query OK, 0 rows affected, 1 warning (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 1

MySQL [business_chatgpt]> ALTER TABLE user_session DROP INDEX idx_user_id_isDel;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

MySQL [business_chatgpt]> ALTER TABLE user_session ADD INDEX idx_user_id_is_del (user_id, is_del);
Query OK, 0 rows affected (0.35 sec)
Records: 0  Duplicates: 0  Warnings: 0

修改后：

CREATE TABLE `user_session` (
  `session_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '当前会话的id',
  `user_id` varchar(36) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '当前会话所属的用户的id',
  `session_name` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '当前会话的名字',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `type` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '聊天的类型，区分普通聊天0、pdf聊天1、冒险游戏聊天2、专家领域3',
  `is_del` tinyint(1) NOT NULL DEFAULT '0' COMMENT '标识记录是否已删除，0-未删除，1-已删除',
  PRIMARY KEY (`session_id`) USING BTREE,
  KEY `idx_user_id` (`user_id`) USING BTREE,
  KEY `idx_user_id_is_del` (`user_id`,`is_del`)
) ENGINE=InnoDB AUTO_INCREMENT=5612 DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC

添加字段

ALTER TABLE `session_chat_record`
ADD COLUMN `data_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '-1' COMMENT '数据类型';

CREATE INDEX idx_version_isDel ON knowledge_base_direct_operation (version, is_del);

查看表的索引

SHOW INDEXES FROM knowledge_base_direct_operation;

修改字段类型

ALTER TABLE knowledge_base_faq MODIFY COLUMN question_id bigint(20) NULL;

ALTER TABLE knowledge_base_direct_operation MODIFY COLUMN question_id bigint(20) NULL;

ALTER TABLE `knowledge_base_faq` MODIFY COLUMN `question_relation_vo_list` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL COMMENT '关联问题列表';




ALTER TABLE `knowledge_base_faq` MODIFY COLUMN `near_question_vo_list` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL COMMENT '相似问法列表';


ALTER TABLE `knowledge_base_faq` MODIFY COLUMN `question_enclosure_vo_list` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL COMMENT '题库附件列表';


ALTER TABLE `knowledge_base_faq` MODIFY COLUMN `rms_routing_answer_vos` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL COMMENT '路由答案列表';


ALTER TABLE `knowledge_base_direct_operation` MODIFY COLUMN `question_relation_vo_list` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL COMMENT '关联问题列表';

ALTER TABLE `knowledge_base_direct_operation` MODIFY COLUMN `near_question_vo_list` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL COMMENT '相似问法列表';


ALTER TABLE `knowledge_base_direct_operation` MODIFY COLUMN `question_enclosure_vo_list` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL COMMENT '题库附件列表';


ALTER TABLE `knowledge_base_direct_operation` MODIFY COLUMN `rms_routing_answer_vos` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL COMMENT '路由答案列表';

ALTER TABLE `knowledge_base_bi` MODIFY param varchar(512) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '参数';


ALTER TABLE `knowledge_base_bi_source` MODIFY param varchar(512) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '参数';

ALTER TABLE knowledge_base_faq 
ADD COLUMN `source` tinyint(1) NOT NULL DEFAULT '1' COMMENT '来源 1：喜鹊声声，2：知识库后台',
ADD COLUMN `status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '状态 0：同步喜鹊声声数据，1：知识库后台录入原始数据';