mysql中的正则表达式

?

1 2 3 4 5 6 7

mysql> SELECT * FROM pet WHERE name LIKE '_____'; +-------+--------+---------+------+------------+-------+ | name  | owner  | species | sex  | birth      | death | +-------+--------+---------+------+------------+-------+ | Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  | | Buffy | Harold | dog     | f    | 1989-05-13 | NULL  | +-------+--------+---------+------+------------+-------+

　　

?

1 2 3 4 5 6 7

mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b'); +--------+--------+---------+------+------------+------------+ | name   | owner  | species | sex  | birth      | death      | +--------+--------+---------+------+------------+------------+ | Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       | | Bowser | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 | +--------+--------+---------+------+------------+------------+

　　

?

1 2 3

SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b' COLLATE utf8mb4_0900_as_cs); SELECT * FROM pet WHERE REGEXP_LIKE(name, BINARY '^b'); SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b', 'c');

　　

mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, 'w');
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| Claws    | Gwen  | cat     | m    | 1994-03-17 | NULL       |
| Bowser   | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL       |
+----------+-------+---------+------+------------+------------+

?

1 2 3 4 5 6 7

mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.....$'); +-------+--------+---------+------+------------+-------+ | name  | owner  | species | sex  | birth      | death | +-------+--------+---------+------+------------+-------+ | Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  | | Buffy | Harold | dog     | f    | 1989-05-13 | NULL  | +-------+--------+---------+------+------------+-------+

　　

?

1 2 3 4 5 6 7

mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.{5}$'); +-------+--------+---------+------+------------+-------+ | name  | owner  | species | sex  | birth      | death | +-------+--------+---------+------+------------+-------+ | Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  | | Buffy | Harold | dog     | f    | 1989-05-13 | NULL  | +-------+--------+---------+------+------------+-------+

　　简单复制黏贴几个感觉挺有意思