Mysql将单条记录中一个字段拆分为单个

默语

发布于 2024-11-20 09:59:22

21500

代码可运行

文章被收录于专栏：JAVAJAVA

运行总次数：0

代码可运行

执行sql 插入语句测试

/*
 Navicat Premium Data Transfer

 Source Server         : springcloud
 Source Server Type    : MySQL
 Source Server Version : 50736
 Source Host           : localhost:3306
 Source Schema         : atguigudb

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

 Date: 12/08/2022 11:54:38
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for book
-- ----------------------------
DROP TABLE IF EXISTS `book`;
CREATE TABLE `book`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT '',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of book
-- ----------------------------
INSERT INTO `book` VALUES (1, 'a,B,C,D');
INSERT INTO `book` VALUES (2, 'a,B,C,D');
INSERT INTO `book` VALUES (3, '10,11,25,33');
INSERT INTO `book` VALUES (4, 'd');
INSERT INTO `book` VALUES (5, 'e');

SET FOREIGN_KEY_CHECKS = 1;

拆分全部

 SELECT
	substring_index( substring_index( a.name, ',', b.help_topic_id + 1 ), ',',- 1 ) AS test_val 
FROM
	book a
join mysql.help_topic b  on b.help_topic_id < ( length(a.name ) - length( REPLACE ( a.name, ',', '' ) ) + 1 )

其他都不需要修改如业务需求不一则自行拆分

拆分单个

 SELECT
	substring_index( substring_index( a.name, ',', b.help_topic_id + 1 ), ',',- 1 ) AS test_val 
FROM
	book a
join mysql.help_topic b  on b.help_topic_id < ( length(a.name ) - length( REPLACE ( a.name, ',', '' ) ) + 1 )
WHERE  a.id=3

扩展：

可适当加入非空验证

 SELECT
	substring_index( substring_index( a.name, ',', b.help_topic_id + 1 ), ',',- 1 ) AS test_val 
FROM
	book a
join mysql.help_topic b  on b.help_topic_id < ( length(a.name ) - length( REPLACE ( a.name, ',', '' ) ) + 1 )
WHERE  a.id=3 AND a.name is not null and a.id  is not null

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2022-12-27，如有侵权请联系 cloudcommunity@tencent.com 删除

set