Shulou(Shulou.com)05/31 Report--

This article introduces how to calculate the length of strings in mysql, the content is very detailed, interested friends can refer to it, I hope it can help you.

String length function in mysql

1, GBK code

char_length 1 Chinese character represents a length, such as char_lent ("hello") with length 2, char_lent ("hello") with length 5

length 1 Chinese character needs two bytes, representing two lengths, for example char_lent ("hello") length 4, char_lent ("hello") length 5

bit_length 1 Chinese character needs two bytes, one byte occupies 8 bits, that is, one character represents 16 lengths, for example, char_lent ("hello") has a length of 32, char_lent ("hello") has a length of 40

2. utf8 encoding

char_length 1 Chinese character represents a length, such as char_lent ("hello") with length 2, char_lent ("hello") with length 5

length 1 Chinese character requires three bytes, representing three lengths, such as char_lent ("hello") with length 6, char_lent ("hello") with length 5

bit_length 1 Chinese character requires 3 bytes, and one byte requires 8 bits, that is, one Chinese represents 24 lengths, for example, char_lent ("hello") has a length of 48, char_lent ("hello") has a length of 40

Wildcards in sql statements

1. Commonly used wildcards in mysql are:

wildcard interpretation

% matches one or more characters

_Match a character

[string] Matches any character in string

[^string] does not match any character in string

[! string] does not match any character in string

2. Use of "%"

We usually use mysql wildcards when querying or authorizing mysql.

To extract all categories starting with test, you need to use wildcard %

1select * from category name 'test%';

3. Use of "_"

For example, the query test1_0 starts with a character classification, and the query statement is as follows:

1select * from category where name like "test1_0_";

Note: In the like statement, you cannot use_casually. If the following records exist in the data table, the appeal query will fail, as follows:

1INSERT INTO category (`name`)VALUES('test1a0b');

How to solve this problem, can only transfer the string in the "_" character, if it involves matching queries, followed by the name of the separator as far as possible do not use underscore "_". The above query statement can be changed to:

1select * from category where name like "test1\_0_";

4. Regular matching

If you insert the following record in the above table

1INSERT INTO category (`name`)VALUES('test1_0a');

Appeal inquiry again, will return

If I only need categories ending in numbers, then the query above does not satisfy the condition. At this point, you need to use regular matching.

1select * from category where name REGEXP "test1_0[1-9]";

mysql regular expressions, and other languages are similar, commonly used characters are as follows:

. Matches any single character

[abc]Match any character in abc

[a-z]Matches any lowercase letter

[A-Z]Match any capital letter

[0-9]Match any number

^test means to begin with a test

Test$stands for test

About how to calculate the string length in mysql to share here, I hope the above content can be of some help to everyone, you can learn more knowledge. If you think the article is good, you can share it so that more people can see it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.