MySQL创建函数

应用场景：某些使用场景中，省市区等联动树结构，输入最后一级返回其树形结构整体得场景

MYSQL 存储树形结构的一种方法, 通过 Adjacency List 来实现, 一起来学习下：

BEGIN
  DECLARE superiors VARCHAR(5000) DEFAULT '';
  DECLARE sTemp VARCHAR(36) DEFAULT car_id;
  DECLARE tmpName VARCHAR(20);
    WHILE (sTemp <> '0' ) DO
        SELECT IFNULL(title,'0')title into tmpName FROM car_sort_copy where id = sTemp;    
        SELECT pid into sTemp FROM car_sort_copy where id = sTemp;
      SET superiors = concat(tmpName,',',superiors);
  END WHILE;
  SET superiors = LEFT(superiors,CHARACTER_LENGTH(superiors)-1);
  RETURN superiors;
END

建表语句如下：

/*
Navicat MySQL Data Transfer

Source Server         : 172.28.15.179
Source Server Version : 50732
Source Host           : 172.28.15.179:3306
Source Database       : caselibrary-dev

Target Server Type    : MYSQL
Target Server Version : 50732
File Encoding         : 65001

Date: 2022-08-15 16:31:36
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `car_sort_copy`
-- ----------------------------
DROP TABLE IF EXISTS `car_sort_copy`;
CREATE TABLE `car_sort_copy` (
  `id` varchar(36) NOT NULL COMMENT 'id',
  `title` varchar(100) DEFAULT NULL COMMENT 'title',
  `pid` varchar(36) DEFAULT NULL COMMENT '父级节点',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of car_sort_copy
-- ----------------------------
INSERT INTO `car_sort_copy` VALUES ('1', '中国', '0');
INSERT INTO `car_sort_copy` VALUES ('2', '辽宁省', '1');
INSERT INTO `car_sort_copy` VALUES ('3', '沈阳市', '2');
INSERT INTO `car_sort_copy` VALUES ('4', '和平区', '3');
INSERT INTO `car_sort_copy` VALUES ('5', '1067创作', '4');
INSERT INTO `car_sort_copy` VALUES ('6', '干扰1', '5');
INSERT INTO `car_sort_copy` VALUES ('7', '测试1', '6');