Logo
活死人の行知路

MySQL中的运算

📅 | 📝 533 字
#MySQL

1算术运算符

加法

select 1+1;

建表：

create table count_test(a int, b int);
insert into count_test values(5, 2), (4, 3);

mysql> select a + b from count_test;
+-------+
| a + b |
+-------+
|     7 |
|     7 |
+-------+
2 rows in set (0.00 sec)

mysql> select a + 3 from count_test;^C
mysql> select * from count_test;
+------+------+
| a    | b    |
+------+------+
|    5 |    2 |
|    4 |    3 |
+------+------+
2 rows in set (0.00 sec)

mysql> select a + b from count_test;
+-------+
| a + b |
+-------+
|     7 |
|     7 |
+-------+
2 rows in set (0.00 sec)

mysql> select a + 3 from count_test;
+-------+
| a + 3 |
+-------+
|     8 |
|     7 |
+-------+
2 rows in set (0.00 sec)

减法

select a - b from count_test;

乘法

select 2*2;

除法

select 4 / 2;
select a / b from count_test;

取余

select 4 % 2;
select a % b from count_test;

2比较运算符

create table compare_test(a int, b int) engine=innodb;
insert into compare_test values(1, 1), (3, 2);

=

mysql> select a=b from compare_test;
+------+
| a=b  |
+------+
|    1 |
|    0 |
+------+

<>

select a<>b from compare_test;

>、>=、<、<=

select a>b from compare_test;

between

select a between 2 and 4 from compare_test;

in

select a (1,5,9) from compare_test;

is null

create table null_test (a varchar(10));
insert into null_test values('aaa'), (null);
select a is null from null_test;
select * from null_test;

mysql> select a is null from null_test;
+-----------+
| a is null |
+-----------+
|         0 |
|         1 |
+-----------+
2 rows in set (0.00 sec)

mysql> select * from null_test;
mysql> select * from null_test;
+------+
| a    |
+------+
| aaa  |
| NULL |
+------+
2 rows in set (0.00 sec)

is not null

逻辑运算符

create database test7;
use test7;
create table logic_test(a int, b int)engine=innodb;
insert into logic_test values (2, 2), (1, 2);

逻辑非

select (1+2) != 2;
select (1+2) != 3;
select a != b from logic_test;
select * form logic_test where a != b;

逻辑与

select 1=1 and 2=2;
select 1=1 and 2=1;
select * from logic_test where a=1 and b=2;

逻辑或

select 1=1 or 2=2;
select 1=3 or 2=1;
select * from logic_test where a=1 or b=2;

逻辑异或

select 1=1 xor 2=1;
select 1=1 xor 2=2;
select 1=2 xor 2=3;

位运算

程序中所有的数据都是以二进制形式存储的，位运算就是对这些二进制数进行运算。

位与&

select 5&6;

对5和6两个数字的二进制进行逻辑与运算，运算后十进制展示。

位或|

select 5|6;

对5和6两个数字的二进制进行逻辑或运算，运算后十进制展示。

位异或^

select 5^6;

对5和6两个数字的二进制进行逻辑异或运算，运算后十进制展示。

位去反~

select ~8;

对 数字的二进制位取反，由于操作系统是64位的，8的二进制前面的数会默认补0。

位左移«

select 5<<1;

位右移«

select 5>>1;

运算符优先级

根据您提供的图片内容，关于运算符优先级（通常适用于 MySQL 等数据库）的 Markdown 表格如下：

优先级（从高到低）运算符
1()
2!
3- (负号), ~ (按位取反)
4^ (按位异或)
5*, /, %
6+, -
7>>, <<
8&
9| (按位或)
10= (比较运算符), <=>, <, <=, >, >=, !=, <>, IN, IS NULL, LIKE, REGEXP
11BETWEEN AND, CASE, WHEN, THEN, ELSE
12NOT
13&&, AND
14XOR
15||, OR
16= (赋值运算), :=

💡 核心建议：

虽然了解运算符优先级是有帮助的，但在实际编写 SQL 语句或代码时，强烈建议使用括号 () 来明确计算顺序。 这样不仅可以避免因为优先级记忆错误导致的代码 Bug，还能极大地提升 SQL 语句的可读性，让其他维护代码的开发者能一眼看懂您的逻辑。

函数

时间函数

  • CURDATE()
  • CURTIME()
  • NOW()
  • ADDDATE()
  • ADDTIME()
  • UNIX_TIMESTAMP()
  • FROM_UNIXTIME()
select curdate();
select current_date();
select current_date;
select current_time();
select current_time;
select adddate("2026-01-01", 20);
select addtime("2026-01-01 00:00:00", '02:30:00');
select unix_timestamp('2026-01-01 00:00:00');
select unix_timestamp();