MySQL - 交叉连接

MySQL - 交叉连接 (CROSS JOIN)

理解 MySQL 交叉连接 (CROSS JOIN)

MySQL CROSS JOIN 会生成所连接表的笛卡尔积。结果是一个表，其中第一个表中的每一行都与第二个表中的每一行组合。如果第一个表有 N 行，第二个表有 M 行，则结果表将有 N * M 行。

这种连接类型没有 ON 子句，因为它不基于条件匹配行。它只是简单地生成来自被连接表的行的所有可能组合。

警告：使用 CROSS JOIN 务必极其谨慎。连接即使是中等大小的表，也可能导致产生海量行数，消耗大量的服务器资源，并可能导致您的应用程序崩溃。这通常是逻辑错误的迹象，例如在原本打算使用 INNER JOIN 的情况下忘记了 ON 子句。

语法

CROSS JOIN 的基本语法很简单：

SELECT column_name(s)
FROM table1
CROSS JOIN table2;

一种较旧的隐式语法也能产生笛卡尔积，但为了清晰起见，更推荐使用显式的 CROSS JOIN 语法：

-- Implicit syntax, less readable and not recommended
-- 隐式语法，可读性较差，不推荐使用
SELECT column_name(s)
FROM table1, table2;

语法和基本示例

让我们考虑一个场景，我们想生成衬衫尺寸和颜色的所有可能组合。首先，我们创建两个简单的表。

CREATE TABLE sizes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    size_name VARCHAR(10) NOT NULL
);

CREATE TABLE colors (
    id INT PRIMARY KEY AUTO_INCREMENT,
    color_name VARCHAR(20) NOT NULL
);

现在，我们用数据填充它们。

INSERT INTO sizes (size_name) VALUES ('Small'), ('Medium'), ('Large');

INSERT INTO colors (color_name) VALUES ('Red'), ('Blue');

sizes 表有 3 行，而 colors 表有 2 行。现在，让我们执行 CROSS JOIN。

SELECT
    s.size_name,
    c.color_name
FROM sizes AS s
CROSS JOIN colors AS c;

输出

结果是笛卡尔积，包含 3 * 2 = 6 行，代表所有可能的组合。

size_name	color_name
Small	Red
Small	Blue
Medium	Red
Medium	Blue
Large	Red
Large	Blue

实际应用和性能警告

虽然 CROSS JOIN 经常被意外使用，但它也有合法的用例：

• 生成数据：适用于创建全面的测试数据或用所有可能的属性组合填充表。
• 报告：在数据仓库中，它可用于创建基础组合集（例如，所有商店与所有日期），然后将真实数据连接到该集上。
• 数学计算：某些复杂计算可能需要生成两个数据集之间所有可能的对集合。

连接多个表

您可以 CROSS JOIN 两个以上的表。结果集中的行数将是所有表中行数的乘积。例如，如果我们添加一个包含 2 种类型的 fabric 表，连接 sizes（3 行）、colors（2 行）和 fabric（2 行）将产生 3 * 2 * 2 = 12 行。

CREATE TABLE fabrics (fabric_name VARCHAR(20));
INSERT INTO fabrics VALUES ('Cotton'), ('Polyester');

SELECT
    s.size_name,
    c.color_name,
    f.fabric_name
FROM sizes AS s
CROSS JOIN colors AS c
CROSS JOIN fabrics AS f;

此查询生成所有 12 种可能的衬衫变体。

在应用程序代码中使用交叉连接 (CROSS JOIN)

从应用程序执行 CROSS JOIN 与执行任何其他 SELECT 查询相同。请记住优雅地处理可能非常大的结果集，以避免应用程序内存不足。

语法

PHP (PDO)
Node.js (mysql2)
Java (JDBC)
Python (mysql-connector)



To perform a cross join in PHP, execute the SQL query using a secure PDO connection.

```php
<?php
// ... (use the same PDO connection setup as in previous examples)
// ...（使用与之前示例相同的 PDO 连接设置）

try {
    $pdo = new PDO($dsn, $user, $pass, $options);

    $sql = "SELECT s.size_name, c.color_name FROM sizes AS s CROSS JOIN colors AS c ORDER BY s.size_name, c.color_name";

    $stmt = $pdo->query($sql);

    echo "All Product Combinations:\n";
    // 输出所有产品组合：
    while ($row = $stmt->fetch()) {
        echo "Size: " . $row['size_name'] . ", Color: " . $row['color_name'] . "\n";
    }

} catch (\PDOException $e) {
    // ... (error handling)
    // ...（错误处理）
}
?>

Use async/await and mysql2/promise to execute the query and process the results.

const mysql = require('mysql2/promise');

async function getCombinations() {
    let connection;
    try {
        connection = await mysql.createConnection({ /* ... connection config ... */ });
        // ...连接配置...

        const sql = 'SELECT s.size_name, c.color_name FROM sizes AS s CROSS JOIN colors AS c ORDER BY s.size_name, c.color_name';
        const [rows] = await connection.execute(sql);

        console.log('All Product Combinations:');
        // 输出所有产品组合：
        rows.forEach(row => {
            console.log(`Size: ${row.size_name}, Color: ${row.color_name}`);
        });

    } catch (error) {
        console.error('Failed to get combinations:', error);
        // 无法获取组合：
    } finally {
        if (connection) await connection.end();
    }
}

getCombinations();

Use a standard JDBC Statement or PreparedStatement to run the cross join query.

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

public class CrossJoinExample {
    public static void main(String[] args) {
        String sql = "SELECT s.size_name, c.color_name FROM sizes AS s CROSS JOIN colors AS c ORDER BY s.size_name, c.color_name";

        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            System.out.println("All Product Combinations:");
            // 输出所有产品组合：
            while (rs.next()) {
                System.out.println(
                    "Size: " + rs.getString("size_name") +
                    ", Color: " + rs.getString("color_name"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Use the mysql-connector-python library to execute the query and fetch the results.

import mysql.connector

config = { /* ... connection config ... */ }
// ...连接配置...

try:
    with mysql.connector.connect(**config) as cnx:
        with cnx.cursor(dictionary=True) as cursor:
            query = "SELECT s.size_name, c.color_name FROM sizes AS s CROSS JOIN colors AS c ORDER BY s.size_name, c.color_name"
            cursor.execute(query)

            print("All Product Combinations:")
            // 输出所有产品组合：
            for row in cursor.fetchall():
                print(f"Size: {row['size_name']}, Color: {row['color_name']}")

except mysql.connector.Error as err:
    print(err)