MySQL数据库中的LOCATE函数是一种用于查找字符串中子字符串位置的函数。它的语法相对简单，但功能却非常实用。通过LOCATE函数，我们可以快速找到字符串中的特定子字符串，从而进行进一步的数据处理或操作。此函数是多字节安全的，并且只有在至少有一个参数是二进制字符串时才区分大小写。该函数和INSTR函数的执行效果一样，区别在于语法和功能上。
LOCATE函数的语法如下：
LOCATE(substring, string) -- 或 LOCATE(substring, string, start)
其中：
substring：要查找的子字符串。
string：要在其中查找子字符串的字符串。
start：可选参数，指定开始查找的位置，默认为1。
mysql> SELECT LOCATE( 'bar','foobarbar'),LOCATE( 'BAR','foobarbar'),
-> LOCATE('xbar', 'foobar'),LOCATE('bar', 'foobarbar',5);
+----------------------------+----------------------------+--------------------------+------------------------------+
| LOCATE( 'bar','foobarbar') | LOCATE( 'BAR','foobarbar') | LOCATE('xbar', 'foobar') | LOCATE('bar', 'foobarbar',5) |
+----------------------------+----------------------------+--------------------------+------------------------------+
| 4 | 4 | 0 | 7 |
+----------------------------+----------------------------+--------------------------+------------------------------+-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`score` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '张三名', '80');
INSERT INTO `student` VALUES ('2', '李四浩', '90');
INSERT INTO `student` VALUES ('3', '王五期', '70');
INSERT INTO `student` VALUES ('4', '赵六三', '40');
INSERT INTO `student` VALUES ('5', '三七熊', '60');原始数据
mysql> SELECT * from student; +----+-----------+-------+ | id | name | score | +----+-----------+-------+ | 1 | 张三名 | 80 | | 2 | 李四浩 | 90 | | 3 | 王五期 | 70 | | 4 | 赵六三 | 40 | | 5 | 三七熊 | 60 | +----+-----------+-------+
假设我们有一个名为student的表，其中包含一个名为name的字段，我们想要查找所有包含特定子字符串的姓名。我们可以使用INSTR函数来筛选这些地址。例如：
mysql> SELECT * FROM student WHERE LOCATE('张',name) = 1;
+----+-----------+-------+
| id | name | score |
+----+-----------+-------+
| 1 | 张三名 | 80 |
+----+-----------+-------+ | 这将返回所有包含子字符串姓“张”的姓名，就是以"张"开头的。INSTR可以和LIKE语句一样实现模糊查询。其模糊查询效率比LIKE快 |
mysql> SELECT * FROM student WHERE LOCATE('三',name) > 0;
+----+-----------+-------+
| id | name | score |
+----+-----------+-------+
| 1 | 张三名 | 80 |
| 4 | 赵六三 | 40 |
| 5 | 三七熊 | 60 |
+----+-----------+-------+把包含“三”字的姓名都列出来
mysql> SELECT * FROM student WHERE LOCATE('三',name) = 0;
+----+-----------+-------+
| id | name | score |
+----+-----------+-------+
| 2 | 李四浩 | 90 |
| 3 | 王五期 | 70 |
+----+-----------+-------+把姓名不包含“三”字的所以姓名列出来。相当于 NOT LIKE语句。
LOCATE函数的参数顺序为子字符串在前，字符串在后。
INSTR函数的参数顺序为字符串在前，子字符串在后。
LOCATE比INSTR多一个参数
LOCATE比INSTR性能好
在使用LOCATE函数时，有几点需要注意：
LOCATE函数的第三个参数是可选的，默认为1。如果省略第三个参数，将从字符串的开头开始查找。如果提供了第三个参数，将从指定的起始位置开始查找。请注意，起始位置从1开始计数。
如果子字符串或字符串为NULL，或者字符串长度小于子字符串长度，LOCATE函数将返回0。
LOCATE函数只能用于处理文本数据类型（如CHAR、VARCHAR等）。对于其他数据类型（如数值或日期），请先将其转换为文本类型。
在使用LOCATE函数时，请注意性能问题。对于非常大的数据集，使用其他方法（如使用LIKE运算符）可能会更有效。
