The usage of MySQL's own function
This article mainly explains "the usage of MySQL's own function". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn the usage of MySQL's own function.
1. IFNULL
Usage instruction
IFNULL (expr1,expr2)
If expr1 is not NULL,IFNULL () returns expr1, otherwise it returns expr2.
IFNULL () returns a number or string value, depending on the context in which it is used.
Let's start with some examples:
Mysql > SELECT IFNULL (1J0)
+-+
| | IFNULL (1line 0) |
+-+
| | 1 |
+-+
● because expr1 is 1, not NULL, so the function returns 1. 0. We can try again, if expr1 is NULL, can we return the second parameter? You might as well let expr1 = 1 impulse 0, and since the divisor is 0, the result is NULL.
Mysql > SELECT IFNULL (1Grammer 0recording NowaMagic)
+-+
| | IFNULL (1Universe 0 recording NowaMagic') |
+-+
| | NowaMagic |
+-+
If the previous parameter is NULL, the second parameter, NowaMagic, is returned.
Second, MID () function
The SQL MID () function is used to get part of a string. This function is supported by MySQL, but not by MS SQL Server and Oracle. In SQL Server, Oracle database, we can use SQL SUBSTRING function or SQL SUBSTR function instead.
The syntax of the MID () function is:
SELECT MID (ColumnName, Start [, Length]) FROM TableName
Note: the string starts at 1 instead of 0 and the length is optional. If not provided, the MID () function returns the rest of the string.
Let's take a simple example:
Mysql > SELECT MID ('NowaMagic', 5,5)
+-- +
| | MID ('NowaMagic', 5,5) | |
+-- +
| | Magic |
+-- +
Third, ORD () function
The ORD () function returns the ASCII value of the first character of the string.
Syntax: ORD (string)
Here are some simple examples:
Mysql > SELECT ORD ('i')
+-+
| | ORD ('i') |
+-+
| | 105 |
+-+
Or:
Mysql > SELECT ORD ('NowaMagic')
+-+
| | ORD ('NowaMagic') | |
+-+
| | 78 |
+-+
What about Chinese characters?
Mysql > SELECT ORD ('Concise Modern Magic')
+-+
| | ORD ('Concise Modern Magic') |
+-+
| | 15183488 |
+-+
Mysql > SELECT ORD ('Jane')
+-+
| | ORD ('Jane') |
+-+
| | 15183488 |
+-+
Why is it as long as eight digits? The reason is the character set used by the database, where the database uses the UTF-8,16 bit to represent a symbol.
By the way, post the execution statement of SQL:
Mysql > SHOW CHARACTER SET
+-- +
| | Charset | Description | Default collation | Maxlen | |
+-- +
| | big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| | dec8 | DEC West European | dec8_swedish_ci | 1 |
| | cp850 | DOS West European | cp850_general_ci | 1 |
| | hp8 | HP West European | hp8_english_ci | 1 |
| | koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| | latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| | latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| | swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| | ascii | US ASCII | ascii_general_ci | 1 |
| | ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| | sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| | hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| | tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| | euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| | koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| | gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| | greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| | cp1250 | Windows Central European | cp1250_general_ci | 1 |
| | gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| | latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| | armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| | utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| | ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| | cp866 | DOS Russian | cp866_general_ci | 1 |
| | keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| | macce | Mac Central European | macce_general_ci | 1 |
| | macroman | Mac West European | macroman_general_ci | 1 |
| | cp852 | DOS Central European | cp852_general_ci | 1 |
| | latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| | utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
| | cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| | utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| | cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| | cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| | utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
| | binary | Binary pseudo charset | binary | 1 |
| | geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| | cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| | eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
+-- +
At this point, I believe you have a deeper understanding of "the use of MySQL built-in functions". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!
