I Basic introduction

   regular expression describes a string matching rule. Regular expression itself is a string. This string is used to describe and define matching rules to match a series of strings that meet a certain syntactic rule. In development, regular expressions are often used to retrieve and replace text that conforms to a certain rule.
  MySQL supports string matching with regular expressions through the REGEXP keyword.

II^ Usage of

^Is to start matching in front of the string, which can be understood as matching prefix

^Match at the beginning of the string

select  'abc' regexp '^a';

^In fact, it is the child character that matches the beginning. Because abc starts with a, its result is 1

Of course, if we want to match the prefix string, we need to add parentheses ();

select 'abc' regexp '^(ab)';
select 'abc' regexp '^(ac)';

abc is prefixed with ac, so the first one returns 1 and the second range is 0.

Note: a symbol ^ can only match one character in ^ mode. If you want to match a string in ^ mode, you should treat it as a whole. We use parentheses () to represent it as a whole, including the following expressions.

III$ Usage of

$actually starts matching at the end of the character, which can be understood as matching suffix

$starts matching at the end of the string

select 'abc' regexp 'a$';
select 'abc' regexp 'c$';

$is used to match the trailing characters. Because abc ends in c, the first result is 0 and the second is 1

Similarly, if our suffix is a string, we need parentheses.

select 'abc' regexp '(bc)$';
select 'abc' regexp '(ac)$';

The suffix of abc is bc, so the first result is 1 and the second is 0.

IV Usage of

. can match any string except \ n, which can be understood as offsetting any character. For example, we use ' b 'yes
’abc 'line matches Matches the first character a, and b just matches, so its result is true.

give an example:
. match any string

select 'abc' regexp '.b';
select 'abc' regexp '.c';
select 'abc' regexp '.a';

V Usage of [...] and [^...]

[...] is used to:
   match any character contained, that is, as long as it can match one character in [].

[...] matches any single character in parentheses

select 'abc' regexp '[xyz]';
select 'abc' regexp '[xaz]';

[^...] is used to:
   matching is any character contained, that is, matching contains characters not contained in []. For example: [^ abc] can match d in abcd.

At the same time, we should note that ^ has the meaning of negation only when it is in [].

select 'a' regexp '[^abc]';
select 'x' regexp '[^abc]';
select 'abc' regexp '[^a]';

Vi* Usage of and +

’*I think the official description of 'is a little ambiguous. The main ambiguity is his description of +.

It is said here that * matches 0 or more times, while + matches one or more times. This description is easy to make people think that it distinguishes 1 time from multiple times, and we will also distinguish 1 time from multiple times when learning mathematical probability theory, so it is easy to understand, and * matches 0 and multiple times. If it matches 1 time, it will not work.
But in fact, this is not the case. Finally, I checked other people's blogs and found that it means 0 to n times, that is, more than 0 times.

usage

select 'stab'regexp '[.ta*b]';
select 'stb'regexp '[.ta*b]';
select ''regexp '[.ta*b]';
select ''regexp '[a*]';

+Usage of:

select 'stab' regexp '.ta+b';
select 'stb' regexp '.ta+b';

7, What? Usage of

？ The function of is to match 0 or 1, it is true, and other cases are false.

give an example:

select 'stb' regexp '.ta?b';
select 'stab' regexp '.ta?b';
select 'staab' regexp '.ta?b';

VIII| Usage of

|The function of is to match one of them, such as a|b, which requires one of a and B in the target string.

select 'a' regexp 'a|b';
select 'b' regexp 'a|b';
select 'b' regexp '^(a|b)';
select 'a' regexp '^(a|b)';
select 'c' regexp '^(a|b)';

IX Usage of {}

{} has two forms:

1. {n} Indicates that a character has been matched n times
2. {n, m} indicates that a character occurs n to m times

give an example:
u{n} matches n u

select 'auuuuuuub' regexp 'u{7}';
select 'auuuuuuub' regexp 'au{7}b';

u{n,m} matches n to m u

select 'auuuub' regexp 'au{3,5}b';
select 'auuuuub' regexp 'au{3,5}b';
select 'auuuuuub' regexp 'au{3,5}b';