解决问题:根据厂家编号分类 没有填写的厂家编号默认为一组 称之为'其他'
建表给某字段 默认字符串
CREATE TABLE `t_device_list` ( `device_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '终端ID', `device_manufacturer_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'cs999999' COMMENT '终端厂商编号', `business_terminal` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT 'bt999999' COMMENT '业务终端(A:集中器,B:配变终端,C:二遥,D:三遥,E:其他)', `network_port` varchar(255) CHARACTER SET utf16le COLLATE utf16le_general_ci NULL DEFAULT NULL COMMENT '网管端口', PRIMARY KEY (`device_id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '终端设备基本信息 不会变化的信息存储于此表' ROW_FORMAT = Compact;
修改表 给某字段 默认字符串
alter table t_device_list alter column business_terminal set default 'bt999999'; alter table t_device_list alter column device_manufacturer_id set default 'cs999999';
建表给某字段 默认当前时间
CREATE TABLE `t_device_manufacturer` ( `id` int(20) NOT NULL AUTO_INCREMENT COMMENT '主键', `device_manufacturer_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '厂家id', `device_manufacturer_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '厂家名称', `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间', `update_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间', PRIMARY KEY (`id`) USING BTREE, INDEX `index_id_name`(`device_manufacturer_id`, `device_manufacturer_name`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '厂家表' ROW_FORMAT = Compact;