SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `expert_copy`
-- ----------------------------
DROP TABLE IF EXISTS `expert_copy`;
CREATE TABLE `expert_copy` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`technology_field1` varchar(500) DEFAULT NULL COMMENT '现熟悉技术领域1',
`technology_field2` varchar(500) DEFAULT NULL COMMENT '现熟悉技术领域2',
`technology_field3` varchar(500) DEFAULT NULL COMMENT '现熟悉技术领域3',
`technology_field4` varchar(500) DEFAULT NULL COMMENT '现熟悉技术领域4',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=519 DEFAULT CHARSET=utf8 COMMENT='专家库; InnoDB free: 230400 kB';-- ----------------------------
-- Records of expert_copy
-- ----------------------------
INSERT INTO `expert_copy` VALUES ('1', '1、桥梁工程->2、公路路面工程->3、公路路基工程->4、隧道工程', null, null, null);
mysql如何将technology_field1字段以“->”为拆分点分开 4个字段并只要中文字,分别存放在technology_field1,technology_field2等,
-- ----------------------------
-- Table structure for `expert_copy`
-- ----------------------------
DROP TABLE IF EXISTS `expert_copy`;
CREATE TABLE `expert_copy` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`technology_field1` varchar(500) DEFAULT NULL COMMENT '现熟悉技术领域1',
`technology_field2` varchar(500) DEFAULT NULL COMMENT '现熟悉技术领域2',
`technology_field3` varchar(500) DEFAULT NULL COMMENT '现熟悉技术领域3',
`technology_field4` varchar(500) DEFAULT NULL COMMENT '现熟悉技术领域4',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=519 DEFAULT CHARSET=utf8 COMMENT='专家库; InnoDB free: 230400 kB';-- ----------------------------
-- Records of expert_copy
-- ----------------------------
INSERT INTO `expert_copy` VALUES ('1', '1、桥梁工程->2、公路路面工程->3、公路路基工程->4、隧道工程', null, null, null);
mysql如何将technology_field1字段以“->”为拆分点分开 4个字段并只要中文字,分别存放在technology_field1,technology_field2等,
问题说明越详细,回答也会越准确!参见如何提问。(提问的智慧)
-- ----------------------------
-- Table structure for `expert_copy`
-- ----------------------------
DROP TABLE IF EXISTS `expert_copy`;
CREATE TABLE `expert_copy` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`technology_field1` varchar(500) DEFAULT NULL COMMENT '现熟悉技术领域1',
`technology_field2` varchar(500) DEFAULT NULL COMMENT '现熟悉技术领域2',
`technology_field3` varchar(500) DEFAULT NULL COMMENT '现熟悉技术领域3',
`technology_field4` varchar(500) DEFAULT NULL COMMENT '现熟悉技术领域4',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=519 DEFAULT CHARSET=utf8 COMMENT='专家库; InnoDB free: 230400 kB';-- ----------------------------
-- Records of expert_copy
-- ----------------------------
INSERT INTO `expert_copy` VALUES ('1', '1、桥梁工程->2、公路路面工程->3、公路路基工程->4、隧道工程', null, null, null);
INSERT INTO `expert_copy` VALUES ('1', '1、气象->2、计算机->3、英语->4、', null, null, null);
INSERT INTO `expert_copy` VALUES ('1', '1、公路、铁路、城市地铁/轻轨建设施工技术->2、->3、->4、', null, null, null);
INSERT INTO `expert_copy` VALUES ('1', '1、胸外科->2、介入治疗技术->3、医院管理->4、', null, null, null);
INSERT INTO `expert_copy` VALUES ('1', '1、制药->2、有机合成->3、GMP认证->4、', null, null, null);
+----+------------------------------------------------------------+-------------------+-------------------+-------------------+
| id | technology_field1 | technology_field2 | technology_field3 | technology_field4 |
+----+------------------------------------------------------------+-------------------+-------------------+-------------------+
| 1 | 1、桥梁工程->2、公路路面工程->3、公路路基工程->4、隧道工程 | NULL | NULL | NULL |
| 2 | 1、气象->2、计算机->3、英语->4、 | NULL | NULL | NULL |
| 3 | 1、公路、铁路、城市地铁/轻轨建设施工技术->2、->3、->4、 | NULL | NULL | NULL |
| 4 | 1、胸外科->2、介入治疗技术->3、医院管理->4、 | NULL | NULL | NULL |
| 5 | 1、制药->2、有机合成->3、GMP认证->4、 | NULL | NULL | NULL |
+----+------------------------------------------------------------+-------------------+-------------------+-------------------+
5 rows in set (0.00 sec)mysql> update expert_copy
-> set technology_field2=mid(SUBSTRING_INDEX(SUBSTRING_INDEX(technology_fiel
d1,'->',2),'->',-1),3),
-> technology_field3=mid(SUBSTRING_INDEX(SUBSTRING_INDEX(technology_field1,
'->',3),'->',-1),3),
-> technology_field4=mid(SUBSTRING_INDEX(technology_field1,'->',-1),3),
-> technology_field1=mid(SUBSTRING_INDEX(technology_field1,'->',1),3);
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5 Changed: 5 Warnings: 0mysql> select * from expert_copy;
+----+---------------------------------------+-------------------+-------------------+-------------------+
| id | technology_field1 | technology_field2 | technology_field3 | technology_field4 |
+----+---------------------------------------+-------------------+-------------------+-------------------+
| 1 | 桥梁工程 | 公路路面工程 | 公路路基工程 | 隧道工程 |
| 2 | 气象 | 计算机 | 英语 | |
| 3 | 公路、铁路、城市地铁/轻轨建设施工技术 | | | |
| 4 | 胸外科 | 介入治疗技术 | 医院管理 | |
| 5 | 制药 | 有机合成 | GMP认证 | |
+----+---------------------------------------+-------------------+-------------------+-------------------+
5 rows in set (0.00 sec)mysql>
+----+------------------------------------------------------------+-------------
------+-------------------+-------------------+
| id | technology_field1 | technology_f
ield2 | technology_field3 | technology_field4 |
+----+------------------------------------------------------------+-------------
------+-------------------+-------------------+
| 1 | 1、桥梁工程->2、公路路面工程->3、公路路基工程->4、隧道工程
| NULL | NULL | NULL
|
+----+------------------------------------------------------------+-------------
------+-------------------+-------------------+
1 row in set (0.00 sec)mysql> update expert_copy set
-> technology_field2=(select SUBSTRING_INDEX(substring_Index(technology_fiel
d1,'->',2),'->',-1)),
-> technology_field3=(select SUBSTRING_INDEX(substring_index(technology_fiel
d1,'->',3),'->',-1)),
-> technology_field4=(select substring_Index(technology_field1,'->',-1)),
-> technology_field1=(select substring_Index(technology_field1,'->',1));
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from expert_copy;
+----+-------------------+-------------------+-------------------+--------------
-----+
| id | technology_field1 | technology_field2 | technology_field3 | technology_fi
eld4 |
+----+-------------------+-------------------+-------------------+--------------
-----+
| 1 | 1、桥梁工程 | 2、公路路面工程 | 3、公路路基工
程 | 4、隧道工程 |
+----+-------------------+-------------------+-------------------+--------------
-----+
1 row in set (0.00 sec)