search
HomeDatabaseMysql TutorialMySQL optimization—detailed explanation of operators (picture)

MySQL optimization - detailed explanation of operators (picture)

Safe equal operator ()

This operator performs the same comparison operation as the = operator, but can be used to determine NULL values.

When both operands are NULL, the return value is 1 instead of NULL;

When one operand is NULL, the return value is 0 instead of NULL .

The following are SELECT NULL 1 SELECT10 SELECTNULLNULL Execution result

When both operands are NULL, the return value is 1 instead of NULL;


#LEAST operator

The syntax format is: LEAST (value 1, value 2,...value n), where the value n indicates that there are n values ​​in the parameter list. In the case of two or more arguments, returns the minimum value.

If any independent variable is NULL, the return value of LEAST() is NULL

Use the LEAST operator to determine the size. The SQL statement is as follows:

SELECT LEAST(2,0),LEAST('a','b','c'),LEAST(10,NULL)

As you can see from the results, when the parameter is an integer or floating point number, LEAST will return the smallest value;

When When the parameter is a string, the character with the highest order in the alphabet is returned;

When there is NULL in the comparison value list, the size cannot be determined, and the return value is NULL

GREATEST operator

The syntax format is: GREATEST(value 1, value 2,...value n), where n indicates that there are n values ​​in the parameter list.

In the case of two or more parameters, return the maximum value.

If any independent variable is NULL, the return value of GREATEST() is NULL

Use the GREATEST operator to determine the size. The SQL statement is as follows:

SELECT GREATEST(2,0),GREATEST('a','b','c'),GREATEST(10,NULL)



As you can see from the results, when the parameter is an integer or floating point number, GREATEST will return the largest value;

When When the parameter is a string, the last character in alphabetical order is returned;

When there is NULL in the comparison value list, the size cannot be determined, and the return value is NULL

REGEXP operator

There are no regular functions or operators in SQLSERVER. MYSQL is indeed relatively complete in this regard

is used for matching String, syntax format is: expr REGEXP matching condition, if expr meets the matching condition, return 1;

If it does not meet, return 0;

If either expr or the matching condition is NULL , the result is NULL

Several commonly used wildcard characters:

(1) '^' matches a string starting with the character after this character

(2)' $' matches a string ending with the character after this character

(3) '.' matches any single character

(4) '[...]' matches square brackets any characters within. For example, "[abc]" matches a, b, or c.

The range of characters can use a '-', "[a-z]" matches any letter, and "[0-9]" matches any number

(5) '*' matches zero one or more characters preceding it. For example, "x*" matches any number of '*' characters, "[0-9]*" matches any number of digits,

and ".*" matches any number of any characters.

Use the REGEXP operator to perform string matching operations. The SQL statement is as follows:

SELECT 'ssky' REGEXP '^s','ssky' REGEXP 'y$' ,'ssky' REGEXP '.sky','ssky' REGEXP '[ab]';


As you can see from the results, the specified matching string is ssky.

'^s' means matching any string starting with the letter s, so the matching condition is met and 1 is returned;

'y$' means matching any string ending with the letter y, Therefore, the matching condition is met and 1 is returned;

'.sky'表示匹配任何以sky结尾,字符长度为4的字符串,因此满足匹配条件,返回1;

'^s'表示匹配任何以字母s开头的字符串,因此满足匹配条件,返回1;

'[ab]'表示匹配任何包含字母a或者b的字符串,指定字符串中没有字母a也没有字母b,因此不满足匹配条件,返回0;

注意:正则表达式是一个可以进行复杂查询的强大工具,相对于LIKE字符串匹配,他可以使用更多的通配符类型,查询结果更加灵活

逻辑运算符

逻辑与运算符:AND或者&&

逻辑或运算符:OR或者||

异或运算符:XOR

当任意一个操作数为NULL时,返回值为NULL;对于非NULL的操作数,如果两个操作数都是非0值或者都是0值,则返回结果为0;

如果一个为0值,另一个为非0值,返回结果为1

使用异或运算符XOR进行逻辑判断,SQL语句如下

SELECT 1 XOR 1, 0 XOR 0,1 XOR 0,1 XOR NULL,1 XOR 1 XOR 1


由结果可以看到‘1 XOR 1’和‘0 XOR 0’中运算符两边的操作数都为非零值,或者都是零值,因此返回0;

'1 XOR 0'中两边的操作数,一个为0值,另一个为非0值,返回结果为1;

'1 XOR NULL'中有一个操作数为NULL,返回结果为NULL;

'1 XOR 1 XOR 1'中有多个操作数,运算符相同,因此运算符从左到右依次计算,'1 XOR 1'的结果为0,再与1进行异或运算,因此结果为1。

注意: a XOR b的计算等同于(a AND (NOT b))或者(NOT a AND ( b))

位运算符

由于比较少用到,这里只做简单介绍

位运算符是用来对二进制字节中的位进行测试、移位或者测试处理

MYSQL中提供的位运算有

按位或(|)

按位与(&)

按位异或(^)

按位左移(

按位右移(>>)

按位取反(~):反转所有比特

TIPS:可以使用BIN()=binary函数查看一个十进制数的二进制表示

例如20这个数字 SELECT BIN(20) 

二进制表示为:10100

特别提示

某一些MYSQL中的特殊字符需要用转义字符才能插入数据库,否则产生意料之外的结果。

下面的特殊字符需要在输入时加反斜线符号开头

输入单引号需要:\'

输入双引号需要:\''

输入反斜杠:\\

输入回车符:\r

输入换行符:\n

输入制表符:\tab

输入退格符:\b

在插入这些特殊字符到数据库之前一定要进行转义处理

例如插入一个单引号,加了反斜杠,插入成功

INSERT INTO table_1(NAME) VALUES('\'')

SELECT * FROM table_1



The above is the detailed content of MySQL optimization—detailed explanation of operators (picture). For more information, please follow other related articles on the PHP Chinese website!

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
How does MySQL's licensing compare to other database systems?How does MySQL's licensing compare to other database systems?Apr 25, 2025 am 12:26 AM

MySQL uses a GPL license. 1) The GPL license allows the free use, modification and distribution of MySQL, but the modified distribution must comply with GPL. 2) Commercial licenses can avoid public modifications and are suitable for commercial applications that require confidentiality.

When would you choose InnoDB over MyISAM, and vice versa?When would you choose InnoDB over MyISAM, and vice versa?Apr 25, 2025 am 12:22 AM

The situations when choosing InnoDB instead of MyISAM include: 1) transaction support, 2) high concurrency environment, 3) high data consistency; conversely, the situation when choosing MyISAM includes: 1) mainly read operations, 2) no transaction support is required. InnoDB is suitable for applications that require high data consistency and transaction processing, such as e-commerce platforms, while MyISAM is suitable for read-intensive and transaction-free applications such as blog systems.

Explain the purpose of foreign keys in MySQL.Explain the purpose of foreign keys in MySQL.Apr 25, 2025 am 12:17 AM

In MySQL, the function of foreign keys is to establish the relationship between tables and ensure the consistency and integrity of the data. Foreign keys maintain the effectiveness of data through reference integrity checks and cascading operations. Pay attention to performance optimization and avoid common errors when using them.

What are the different types of indexes in MySQL?What are the different types of indexes in MySQL?Apr 25, 2025 am 12:12 AM

There are four main index types in MySQL: B-Tree index, hash index, full-text index and spatial index. 1.B-Tree index is suitable for range query, sorting and grouping, and is suitable for creation on the name column of the employees table. 2. Hash index is suitable for equivalent queries and is suitable for creation on the id column of the hash_table table of the MEMORY storage engine. 3. Full text index is used for text search, suitable for creation on the content column of the articles table. 4. Spatial index is used for geospatial query, suitable for creation on geom columns of locations table.

How do you create an index in MySQL?How do you create an index in MySQL?Apr 25, 2025 am 12:06 AM

TocreateanindexinMySQL,usetheCREATEINDEXstatement.1)Forasinglecolumn,use"CREATEINDEXidx_lastnameONemployees(lastname);"2)Foracompositeindex,use"CREATEINDEXidx_nameONemployees(lastname,firstname);"3)Forauniqueindex,use"CREATEU

How does MySQL differ from SQLite?How does MySQL differ from SQLite?Apr 24, 2025 am 12:12 AM

The main difference between MySQL and SQLite is the design concept and usage scenarios: 1. MySQL is suitable for large applications and enterprise-level solutions, supporting high performance and high concurrency; 2. SQLite is suitable for mobile applications and desktop software, lightweight and easy to embed.

What are indexes in MySQL, and how do they improve performance?What are indexes in MySQL, and how do they improve performance?Apr 24, 2025 am 12:09 AM

Indexes in MySQL are an ordered structure of one or more columns in a database table, used to speed up data retrieval. 1) Indexes improve query speed by reducing the amount of scanned data. 2) B-Tree index uses a balanced tree structure, which is suitable for range query and sorting. 3) Use CREATEINDEX statements to create indexes, such as CREATEINDEXidx_customer_idONorders(customer_id). 4) Composite indexes can optimize multi-column queries, such as CREATEINDEXidx_customer_orderONorders(customer_id,order_date). 5) Use EXPLAIN to analyze query plans and avoid

Explain how to use transactions in MySQL to ensure data consistency.Explain how to use transactions in MySQL to ensure data consistency.Apr 24, 2025 am 12:09 AM

Using transactions in MySQL ensures data consistency. 1) Start the transaction through STARTTRANSACTION, and then execute SQL operations and submit it with COMMIT or ROLLBACK. 2) Use SAVEPOINT to set a save point to allow partial rollback. 3) Performance optimization suggestions include shortening transaction time, avoiding large-scale queries and using isolation levels reasonably.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Dreamweaver Mac version

Dreamweaver Mac version

Visual web development tools

VSCode Windows 64-bit Download

VSCode Windows 64-bit Download

A free and powerful IDE editor launched by Microsoft

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Safe Exam Browser

Safe Exam Browser

Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools