mysql查询自己的选课信息_[MySQL]查询学生选课的情况（一）

这是我工作遇到的问题，现在自己设计一个简化的类似场景，现实中这样的数据表设计可能有很多不合理的地方。

首先看表结构：

+--------+--------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+--------+--------------+------+-----+---------+-------+

| id | varchar(38) | NO | PRI | NULL | |

| name | varchar(255) | YES | | NULL | |

| course | varchar(300) | YES | | NULL | |

+--------+--------------+------+-----+---------+-------+

这里只是记录学生的ID，名字，还有选课的科目，科目有很多，在没有关联表的情况下，这么多科目只保存在一个字段中，用逗号隔开。

再看一些数据：

+--------------------------------------+--------+--------------------------------+

| id | name | course |

+--------------------------------------+--------+--------------------------------+

| 32268995-f33d-11e4-a31d-089e0140e076 | 张三 | Math,English,Chinese |

| 3d670ef2-f33d-11e4-a31d-089e0140e076 | 李四 | Math,English,Chinese,Algorithm |

| 475d51a6-f33d-11e4-a31d-089e0140e076 | 李五 | Math,English,Algorithm |

| 547fdea0-f33d-11e4-a31d-089e0140e076 | 王小明 | Math,English,Japanese |

| 656a247a-f33d-11e4-a31d-089e0140e076 | 曹达华 | Chesses |

+--------------------------------------+--------+--------------------------------+

那么如何查找到选择了Math课程的学生？

想想使用关联表的时候，张三, 李四, 李五, 王小明这四个人都一条选择了Math这门课的记录，还有其他不是Math的记录。此时要查找选择了Math课程的学生，一般使用IN语句就可以了：

select * from student_course where course IN ('Math');

如果要查找选择了Math或Algorithm课程的学生呢：

select * from student_course where course IN ('Math', 'Algorithm');

如此，回到原来的问题，如果我设计一个类似IN一样的函数，那么就可以解决这个问题了。

这个流程我们可以想象出来，是这样子的：

我们取张三的课程信息Math,English,Chinese，首先切割成Math, English,Chinese三个字段，然后分别与与查找条件做比较，类似'Math'.indexOf('Math');,'Math'.indexOf('English');...

只要找到一个就认为符合查找条件。

同样的，如果要查找选择了Math或Algorithm课程的学生，比较过程就变成了：

'Math,Algorithm'.indexOf('Math');,'Math,Algorithm'.indexOf('English');...

切割函数 getSplitTotalLength, getSplitString

CREATE DEFINER = `root`@`%` FUNCTION `getSplitTotalLength`(`f_string` varchar(500),`f_delimiter` varchar(5))

RETURNS int(11)

BEGIN

# 计算传入字符串能切分成多少段

return 1+(length(f_string) - length(replace(f_string,f_delimiter,'')));

RETURN 0;

END;

CREATE DEFINER = `root`@`%` FUNCTION `getSplitString`(`f_string` varchar(500),`f_delimiter` varchar(5),`f_order` int)

RETURNS varchar(500)

BEGIN

#拆分传入的字符串，分隔符，顺序，返回拆分所得的新字符串

declare result varchar(500) default '';

set result = reverse(substring_index(reverse(substring_index(f_string,f_delimiter,f_order)),f_delimiter,1));

RETURN result;

END;

类似IN的那个函数 isInSearch

CREATE DEFINER=`root`@`%` FUNCTION `isInSearch`(f_course VARCHAR(300), f_string VARCHAR(300)) RETURNS INT

BEGIN

DECLARE len INT DEFAULT 0;

DECLARE idx INT DEFAULT 0;

DECLARE item_code VARCHAR(300) DEFAULT '';

DECLARE item_index INT DEFAULT 0;

IF f_course IS NULL THEN

RETURN 0;

END IF;

SELECT getSplitTotalLength(f_course, ',') INTO len;

label: LOOP

SET idx = idx + 1;

IF idx > len THEN

LEAVE label;

END IF;

SELECT getSplitString(f_course , ',', idx) INTO item_code;

# f_string.indexOf(item_code) > -1 ?

SELECT LOCATE(item_code, f_string) INTO item_index;

IF item_index > 0 THEN

RETURN 1; # got one

END IF;

END LOOP label;

RETURN 0;

END;

这里说下locate函数，locate(item_code, f_string)，如果item_code是f_string的子串，返回的结果大于0，是item_code在f_string的起始下标(从1开始算起)，这个一般的indexOf函数有些不同。

mysql> select locate('Math','Math,Algorithm');

+---------------------------------+

| locate('Math','Math,Algorithm') |

+---------------------------------+

| 1 |

+---------------------------------+

mysql> select locate('Math','Chinese,Math,Algorithm');

+-----------------------------------------+

| locate('Math','Chinese,Math,Algorithm') |

+-----------------------------------------+

| 9 |

+-----------------------------------------+

mysql> select locate('Math','Chinese,Algorithm');

+------------------------------------+

| locate('Math','Chinese,Algorithm') |

+------------------------------------+

| 0 |

+------------------------------------+

可以看到isInSearch函数返回的是INT类似，因为MySQL的IN也是这样的机制。

mysql> select 'Math' in ('Math','Algorightm');

+---------------------------------+

| 'Math' in ('Math','Algorightm') |

+---------------------------------+

| 1 |

+---------------------------------+

mysql> select 'Math' in ('Chinese','Algorightm');

+------------------------------------+

| 'Math' in ('Chinese','Algorightm') |

+------------------------------------+

| 0 |

+------------------------------------+

如果存在返回1，不存在返回0。

在SELECT语句中使用自定义的函数

mysql> select * from student_course where isInSearch(course, 'Math');

+--------------------------------------+--------+--------------------------------+

| id | name | course |

+--------------------------------------+--------+--------------------------------+

| 32268995-f33d-11e4-a31d-089e0140e076 | 张三 | Math,English,Chinese |

| 3d670ef2-f33d-11e4-a31d-089e0140e076 | 李四 | Math,English,Chinese,Algorithm |

| 475d51a6-f33d-11e4-a31d-089e0140e076 | 李五 | Math,English,Algorithm |

| 547fdea0-f33d-11e4-a31d-089e0140e076 | 王小明 | Math,English,Japanese |

+--------------------------------------+--------+--------------------------------+

mysql> select * from student_course where isInSearch(course, 'Chinese,Japanese');

+--------------------------------------+--------+--------------------------------+

| id | name | course |

+--------------------------------------+--------+--------------------------------+

| 32268995-f33d-11e4-a31d-089e0140e076 | 张三 | Math,English,Chinese |

| 3d670ef2-f33d-11e4-a31d-089e0140e076 | 李四 | Math,English,Chinese,Algorithm |

| 547fdea0-f33d-11e4-a31d-089e0140e076 | 王小明 | Math,English,Japanese |

+--------------------------------------+--------+--------------------------------+