MySQL - COALESCE() 函数

MySQL COALESCE() 函数
使用客户端程序的 Coalesce() 函数

有时表中的某条记录可能缺少数据，而用户无法用零填充。在这种情况下，MySQL 允许用户用 NULL 值填充该记录。

NULL 值只是数据库表中的占位符，用于表示缺失值或无法插入数据。

MySQL COALESCE() 函数

MySQL COALESCE() 函数返回表达式列表中的第一个非 NULL 值。它接受多个表达式作为参数，并返回第一个非 NULL 表达式的值。如果所有表达式都为 NULL，则返回 NULL。

当所有参数均为 NOT NULL 时，COALESCE() 函数会根据其数据类型的优先级计算值。例如，在 COALESCE() 函数中，整数始终优先于字符表达式，因此输出结果为整数。

当 COALESCE() 函数用于 MySQL 表时，如果参数表示需要比较的字段名，该函数会比较这些列中的对应值，并检索第一个非空值。

语法

以下是 COALESCE() 函数的基本语法 -

SELECT COALESCE (expression_1, Expression_2, ..., Expression_n)
FROM table_name;

示例

在下面的查询中，我们从传递给 COALESCE() 函数的参数列表中检索第一个非 NULL 值 -

SELECT COALESCE(NULL, NULL, 'Hello', 'Tutorialspoint')
AS RESULT;

输出

以下是获得的输出 -

RESULT
Hello

示例

现在，让我们创建一个名为"CUSTOMERS"的表来存储客户的个人详细信息，包括他们的姓名、年龄、地址和工资，使用以下查询 -

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20),
   AGE INT,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY(ID)
);

现在，使用 INSERT 语句将值插入到该表中，如下所示 -

INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', 32, NULL, NULL ),
(2, 'Khilan', 25, 'Delhi', NULL ),
(3, 'kaushik', 23, 'Kota', NULL ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'MP', NULL ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

以下是获得的 CUSTOMERS 表 -

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	NULL	NULL
2	Khilan	25	Delhi	NULL
3	Kaushik	23	Kota	NULL
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	MP	NULL
7	Muffy	24	Indore	10000.00

现在，我们使用 SELECT 语句检索包含"NAME"、"AGE"和"SALARY"列值的结果集。我们还将"AGE"和"SALARY"作为参数传递给 COALESCE() 函数，返回值将显示在名为"RESULT"的另一列中。−

SELECT NAME, AGE, SALARY,
COALESCE(SALARY, AGE) RESULT
FROM CUSTOMERS;

输出

获得的结果如下所示 −

NAME	AGE	SALARY	RESULT
Ramesh	32	NULL	32.00
Khilan	25	NULL	25.00
Kaushik	23	NULL	23.00
Chaitali	25	6500.00	6500.00
Hardik	27	8500.00	8500.00
Komal	22	NULL	22.00
Muffy	24	10000.00	10000.00

在结果集中，您会注意到"NAME"、"AGE"和"SALARY"值显示正常。但是，"RESULT"列包含"AGE"和"SALARY"列中的第一个非 NULL 值。例如，在第一条记录中，"SALARY"列包含 NULL 值，但"AGE"包含非 NULL 值，因此"RESULT"列显示年龄值。

如果两列都包含非 NULL 值，则 COALESCE() 函数返回最大值。

使用客户端程序执行 Coalesce() 函数

我们也可以使用客户端程序执行 coalesce() 函数。

语法

PHP NodeJS Java Python

要通过 PHP 程序执行 COALESCE() 函数，我们需要使用 mysqli 函数 query() 执行"SELECT"语句，如下所示 -

$sql = "SELECT NAME, AGE, SALARY, COALESCE(SALARY, AGE) RESULT FROM CUSTOMER";
$mysqli->query($sql);

要通过 JavaScript 程序执行 COALESCE() 函数，我们需要使用 mysql2 库的 query() 函数执行"SELECT"语句，如下所示 -

sql = "SELECT NAME, AGE, SALARY, COALESCE(SALARY, AGE) RESULT FROM CUSTOMER";
con.query(sql)

要通过 Java 程序执行 COALESCE() 函数，我们需要使用 JDBC 函数 executeQuery() 执行"SELECT"语句，如下所示 -

String sql = "SELECT NAME, AGE, SALARY, COALESCE(SALARY, AGE) RESULT FROM CUSTOMER";
statement.executeQuery(sql);

要通过 Python 程序执行 COALESCE() 函数，我们需要使用 MySQL Connector/Python 的 execute() 函数执行"SELECT"语句，如下所示 -

coalesce_query = "SELECT NAME, AGE, SALARY, COALESCE(SALARY, AGE) RESULT FROM CUSTOMER";
cursorObj.execute(coalesce_query)

示例

以下是程序 -

PHP NodeJS Java Python

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//创建客户表 CUSTOMER
$sql = 'CREATE TABLE IF NOT EXISTS CUSTOMER ( ID INT NOT NULL, NAME VARCHAR (20), AGE INT, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY(ID) )';
$result = $mysqli->query($sql);
if ($result) {
    echo "Table created successfully...!
";
}

// 将数据插入到创建的表中
$q = "INSERT INTO CUSTOMER (ID,NAME,AGE,ADDRESS,SALARY) VALUES
    (1, 'Ramesh', 32, NULL, NULL ),
    (2, 'Khilan', 25, 'Delhi', NULL ),
    (3, 'kaushik', 23, 'Kota', NULL ),
    (4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
    (5, 'Hardik', 27, 'Bhopal', 8500.00 ),
    (6, 'Komal', 22, 'MP', NULL ),
    (7, 'Muffy', 24, 'Indore', 10000.00 )";
if ($res = $mysqli->query($q)) {
    echo "Data inserted successfully...! 
";
}

$sql = "SELECT * FROM CUSTOMER";
if ($res = $mysqli->query($sql)) {
    echo "
Table's records before coalesce()...!

";
    while ($row = $res->fetch_array()) {
        printf(
            "iD: %d, Name: %s, Age: %d, Address: %s, Salary: %2f",
            $row['ID'], $row['NAME'], $row['AGE'], $row['ADDRESS'], $row['SALARY']
        );
        printf("
");
    }
}
//使用合并函数:
$sql = "SELECT NAME, AGE, SALARY, COALESCE(SALARY, AGE) RESULT FROM CUSTOMER";
if ($res = $mysqli->query($sql)) {
    echo "
Table's records after coalesce()...!

";
    while ($row = $res->fetch_array()) {
        printf(
            "Name: %s, Age: %d, Salary: %2f, Result: %2f",
            $row['NAME'], $row['AGE'], $row['SALARY'], $row['RESULT']
        );
        printf("
");
    }
}
$mysqli->close();

输出

获得的输出如下所示 -

Table's records before coalesce()...!
iD: 1, Name: Ramesh, Age: 32, Address: , Salary: 0.000000
iD: 2, Name: Khilan, Age: 25, Address: Delhi, Salary: 0.000000
iD: 3, Name: kaushik, Age: 23, Address: Kota, Salary: 0.000000
iD: 4, Name: Chaitali, Age: 25, Address: Mumbai, Salary: 6500.000000
iD: 5, Name: Hardik, Age: 27, Address: Bhopal, Salary: 8500.000000
iD: 6, Name: Komal, Age: 22, Address: MP, Salary: 0.000000
iD: 7, Name: Muffy, Age: 24, Address: Indore, Salary: 10000.000000

Table's records after coalesce()...!
Name: Ramesh, Age: 32, Salary: 0.000000, Result: 32.000000
Name: Khilan, Age: 25, Salary: 0.000000, Result: 25.000000
Name: kaushik, Age: 23, Salary: 0.000000, Result: 23.000000
Name: Chaitali, Age: 25, Salary: 6500.000000, Result: 6500.000000
Name: Hardik, Age: 27, Salary: 8500.000000, Result: 8500.000000
Name: Komal, Age: 22, Salary: 0.000000, Result: 22.000000
Name: Muffy, Age: 24, Salary: 10000.000000, Result: 10000.000000

var mysql = require('mysql2');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

// 连接到 MySQL
con.connect(function (err) {
    if (err) throw err;
    console.log("Connected!");
    console.log("--------------------------");
   // 创建新数据库
    sql = "Create Database TUTORIALS";
    con.query(sql);
   sql = "USE TUTORIALS";
    con.query(sql);
   sql = "CREATE TABLE CUSTOMER (ID INT NOT NULL,NAME VARCHAR (20),AGE INT,ADDRESS CHAR (25),SALARY DECIMAL (18, 2),PRIMARY KEY(ID));"
    con.query(sql);
   sql = "INSERT INTO CUSTOMER (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Ramesh', 32, NULL, NULL ),(2, 'Khilan', 25, 'Delhi', NULL ),(3, 'kaushik', 23, 'Kota', NULL ),(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),(5, 'Hardik', 27, 'Bhopal', 8500.00 ),(6, 'Komal', 22, 'MP', NULL ),(7, 'Muffy', 24, 'Indore', 10000.00 );"
    con.query(sql);
   sql = "SELECT * FROM CUSTOMER;"
    con.query(sql, function(err, result){
      if (err) throw err
      console.log("**Records of CUSTOMER Table:**");
      console.log(result);
      console.log("--------------------------");
    });
   //COALESCE函数的用法
    sql = "SELECT NAME, AGE, SALARY, COALESCE(SALARY, AGE) RESULT FROM CUSTOMER;"
    con.query(sql, function(err, result){
      if (err) throw err
      console.log(result);
    });
});

输出

获得的输出如下所示 -

 
Connected!
--------------------------
**Records of CUSTOMER Table:**
[
  { ID: 1, NAME: 'Ramesh', AGE: 32, ADDRESS: null, SALARY: null },
  { ID: 2, NAME: 'Khilan', AGE: 25, ADDRESS: 'Delhi', SALARY: null },
  { ID: 3, NAME: 'kaushik', AGE: 23, ADDRESS: 'Kota', SALARY: null },
  { ID: 4, NAME: 'Chaitali', AGE: 25, ADDRESS: 'Mumbai', SALARY: '6500.00'},
  { ID: 5, NAME: 'Hardik', AGE: 27, ADDRESS: 'Bhopal', SALARY: '8500.00'},
  { ID: 6, NAME: 'Komal', AGE: 22, ADDRESS: 'MP', SALARY: null },
  { ID: 7, NAME: 'Muffy', AGE: 24, ADDRESS: 'Indore', SALARY: '10000.00'}
]
--------------------------
[
  { NAME: 'Ramesh', AGE: 32, SALARY: null, RESULT: '32.00' },
  { NAME: 'Khilan', AGE: 25, SALARY: null, RESULT: '25.00' },
  { NAME: 'kaushik', AGE: 23, SALARY: null, RESULT: '23.00' },
  { NAME: 'Chaitali', AGE: 25, SALARY: '6500.00', RESULT: '6500.00' },
  { NAME: 'Hardik', AGE: 27, SALARY: '8500.00', RESULT: '8500.00' },
  { NAME: 'Komal', AGE: 22, SALARY: null, RESULT: '22.00' },
  { NAME: 'Muffy', AGE: 24, SALARY: '10000.00', RESULT: '10000.00' }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Coalesce {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");
        System.out.println("Table records...!
");
         ResultSet resultSet = statement.executeQuery("SELECT * FROM CUSTOMERS");
         while (resultSet.next()){
            System.out.println("Id: "+resultSet.getString(1)+" Name: "+resultSet.getString(2)+" Age: "+resultSet.getString(3)+" Address: "+resultSet.getString(4)+" Salary: "+resultSet.getString(4));
         }
        //使用合并函数:
         System.out.println("
After using Coalesce function...!
");
         String sql = "SELECT NAME, AGE, SALARY, COALESCE(SALARY, AGE) RESULT FROM CUSTOMERS";
         ResultSet resultSet1 = statement.executeQuery(sql);
         while (resultSet1.next()){
            System.out.println("Name: "+resultSet1.getString(1)+" Age: "+resultSet1.getString(2)+" Salary: "+resultSet1.getString(3)+" Result: "+resultSet1.getString(4));
         }
         connection.close();
      } catch (Exception e) {
         e.printStackTrace();
      }
   }
}

输出

获得的输出如下所示 -

Connected successfully...!
Table records...!

Id: 1 Name: Ramesh Age: 32 Address: Ahmedabad Salary: Ahmedabad
Id: 2 Name: Khilan Age: 25 Address: Delhi Salary: Delhi
Id: 3 Name: kaushik Age: 23 Address: Kota Salary: Kota
Id: 4 Name: Chaitali Age: 25 Address: Mumbai Salary: Mumbai
Id: 5 Name: Hardik Age: 27 Address: Bhopal Salary: Bhopal
Id: 6 Name: Komal Age: 22 Address: MP Salary: MP
Id: 7 Name: Muffy Age: 24 Address: Indore Salary: Indore

After using Coalesce function...!

Name: Ramesh Age: 32 Salary: 2000.00 Result: 2000.00
Name: Khilan Age: 25 Salary: 1500.00 Result: 1500.00
Name: kaushik Age: 23 Salary: 2000.00 Result: 2000.00
Name: Chaitali Age: 25 Salary: 6500.00 Result: 6500.00
Name: Hardik Age: 27 Salary: 8500.00 Result: 8500.00
Name: Komal Age: 22 Salary: 4500.00 Result: 4500.00
Name: Muffy Age: 24 Salary: 10000.00 Result: 10000.00

import mysql.connector
# 建立连接 
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
# 创建游标对象 
cursorObj = connection.cursor()
# 创建"CUSTOMER"表
create_table_query = '''
CREATE TABLE CUSTOMER (
    ID INT NOT NULL,
    NAME VARCHAR(20),
    AGE INT,
    ADDRESS CHAR(25),
    SALARY DECIMAL(18, 2),
    PRIMARY KEY(ID)
);
'''
cursorObj.execute(create_table_query)
print("Table 'CUSTOMER' is created successfully!")
# 将记录插入"CUSTOMER"表
insert_query = "INSERT INTO CUSTOMER (ID, NAME, AGE, ADDRESS, SALARY) VALUES (%s, %s, %s, %s, %s);"
values = [
    (1, 'Ramesh', 32, None, None),
    (2, 'Khilan', 25, 'Delhi', None),
    (3, 'Kaushik', 23, 'Kota', None),
    (4, 'Chaitali', 25, 'Mumbai', 6500.00),
    (5, 'Hardik', 27, 'Bhopal', 8500.00),
    (6, 'Komal', 22, 'MP', None),
    (7, 'Muffy', 24, 'Indore', 10000.00)
]
cursorObj.executemany(insert_query, values)
print("Values inserted successfully!")
# 显示"CUSTOMER"表的内容
display_table_query = "SELECT * FROM CUSTOMER;"
cursorObj.execute(display_table_query)
results = cursorObj.fetchall()
print("
CUSTOMER Table:")
for result in results:
    print(result)
# 合并表达式
coalesce_query = "SELECT NAME, AGE, SALARY, COALESCE(SALARY, AGE) RESULT FROM CUSTOMER;"
cursorObj.execute(coalesce_query)
results = cursorObj.fetchall()
print("
Coalesce Table:")
for result in results:
    print(result)
# 关闭游标和连接
cursorObj.close()
connection.close()

输出

获得的输出如下所示 -

Table 'CUSTOMER' is created successfully!
Values inserted successfully!

CUSTOMER Table:
(1, 'Ramesh', 32, None, None)
(2, 'Khilan', 25, 'Delhi', None)
(3, 'Kaushik', 23, 'Kota', None)
(4, 'Chaitali', 25, 'Mumbai', Decimal('6500.00'))
(5, 'Hardik', 27, 'Bhopal', Decimal('8500.00'))
(6, 'Komal', 22, 'MP', None)
(7, 'Muffy', 24, 'Indore', Decimal('10000.00'))

Coalesce Table:
('Ramesh', 32, None, Decimal('32.00'))
('Khilan', 25, None, Decimal('25.00'))
('Kaushik', 23, None, Decimal('23.00'))
('Chaitali', 25, Decimal('6500.00'), Decimal('6500.00'))
('Hardik', 27, Decimal('8500.00'), Decimal('8500.00'))
('Komal', 22, None, Decimal('22.00'))
('Muffy', 24, Decimal('10000.00'), Decimal('10000.00'))

MySQL 8 教程

MySQL 8 数据库

MySQL 8 用户

MySQL 8 表

MySQL 8 查询

MySQL 8 视图

MySQL 8 索引

MySQL 运算符和子句

MySQL 连接

MySQL 键

MySQL 触发器

MySQL 8 数据类型

MySQL 正则表达式

MySQL 全文搜索

MySQL8 函数和运算符

MySQL 8 其他概念

MySQL 8 实用资源

MySQL - COALESCE() 函数

MySQL COALESCE() 函数

语法

示例

输出

示例

输出

使用客户端程序执行 Coalesce() 函数

语法

示例

输出

输出

输出

输出

颜色选择器

读后有收获微信请站长喝咖啡

错误报告

您的建议:

感谢您的帮助！