✅ DuckDB 的核心特点：

✅springboot集成duckdb示例：

1.增加引用

        <!-- DuckDB JDBC -->
<!--        <dependency>-->
<!--            <groupId>org.duckdb</groupId>-->
<!--            <artifactId>duckdb_jdbc</artifactId>-->
<!--            <version>1.3.2.1</version>-->
<!--        </dependency>-->

2. 增加配置

  datasource:
    dynamic:
      primary: master #设置默认的数据源或者数据源组,默认值即为master
      strict: false #严格匹配数据源,默认false. true未匹配到指定数据源时抛异常,false使用默认数据源
      datasource:
        master:
          url: jdbc:mysql://${MYSQL_HOST}:${MYSQL_PORT}/${MYSQL_DATABASE}?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&rewriteBatchedStatements=true
          username: ${MYSQL_USERNAME}
          password: ${MYSQL_PASSWORD}
          driver-class-name: com.mysql.cj.jdbc.Driver
          type: com.alibaba.druid.pool.DruidDataSource
#        duck:
#          url: 'jdbc:duckdb::memory:'  # 内存模式，重启丢；可改为 jdbc:duckdb:/data/ana.duckdb
#          driver-class-name: org.duckdb.DuckDBDriver
#          type: com.zaxxer.hikari.HikariDataSource   # 用 Hikari 没问题
#          # 下面 3 项必须填，否则 DS 启动校验失败
#          username: sa
#          password: ''

3.初始化数据

package com.artefact.framework.handlers;


import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
import jakarta.annotation.Resource;
import lombok.extern.log4j.Log4j2;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.CommandLineRunner;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

@Component
@Log4j2
public class DuckdbLoadRunner implements CommandLineRunner {
    @Resource
    private DataSource dataSource;   //


    @Value("${MYSQL_HOST}")
    private String MYSQL_HOST;
    @Value("${MYSQL_PORT}")
    private String MYSQL_PORT;
    @Value("${MYSQL_DATABASE}")
    private String MYSQL_DATABASE;
    @Value("${MYSQL_USERNAME}")
    private String MYSQL_USERNAME;
    @Value("${MYSQL_PASSWORD}")
    private String MYSQL_PASSWORD;

    @Override
    public void run(String... args) throws Exception {

        DataSource duckDs = ((DynamicRoutingDataSource) dataSource).getDataSource("duck");
        DataSource mysqlDs = ((DynamicRoutingDataSource) dataSource).getDataSource("master");

        try (Connection duck = duckDs.getConnection();
             Connection mysql = mysqlDs.getConnection()) {

            try (Statement stmt = duck.createStatement()) {
                // 1. 加载扩展
                stmt.execute("INSTALL mysql;");
                stmt.execute("LOAD mysql;");

                // 2. 建立 MySQL 连接串（与 yml 里保持一致）
                String mysqlConn = String.format("host=%s port=%s user=%s password=%s database=%s", MYSQL_HOST, MYSQL_PORT, MYSQL_USERNAME, MYSQL_PASSWORD, MYSQL_DATABASE);

                // 先 ATTACH（只需一次）
                String attachSql = "ATTACH '" + mysqlConn + "' AS mydb (TYPE mysql);";
                stmt.execute(attachSql);

                // 3. 需要分析的表
                List<String> tables = List.of("product", "style", "style_option", "sku");
                for (String t : tables) {
                    long start = System.currentTimeMillis();
                    duck.createStatement().execute(
                            "CREATE OR REPLACE TABLE " + t + " AS SELECT * FROM mydb." + t);
                    log.info("表 {} 导入完成，耗时 {} ms", t, System.currentTimeMillis() - start);
                }
                log.info("===== DuckDB 全量镜像完毕 =====");
            } catch (SQLException e) {
                log.error("DuckDB 导入失败", e);
            }
        } catch (Exception e) {
            log.error("DuckDB 导入失败", e);
        }
    }
}

4.duckdb sql注意事项（语法 和MYSQL不一样，坑很多）

例如：

SELECT COUNT(DISTINCT a.style_code) AS ofStyle
FROM style_option a
LEFT JOIN style s ON a.style_code = s.style_code
CROSS JOIN json_each(a.colour_family) AS cfj(val)
WHERE cfj.val IN ('"Black"')
LIMIT 10 OFFSET 10;

注意事项：

1.CROSS JOIN

2.json_each

3. json_each展开后，字符串默认加了“XXX”，所以直接用In/=匹配XXX是无法匹配上的，必须要用“XXX”，如：WHERE cfj.val IN ('"Black"')

更多坑。。。