MyBatis 在 Java 业务开发领域可以说是首选的持久化框架，本文主要总结下 MyBatis 的核心配置和源码解析。

MyBatis 产品组成

MyBatis 常用的产品组成汇总如下：

MyBatis 核心架构

MyBatis 语句执行时的层次结构：

涉及的主要 API 如下：

Executor

StatementHandler

ParameterHandler

ResultSetHandler

TypeHandler

使用方式

基于 Statement ID 的传统方式

基于 Mapper 接口的推荐方式

参考

http://www.mybatis.org/

https://github.com/mybatis

《MyBatis 从入门到精通》

《MyBatis 技术内幕》

本文梳理 Spring 事务管理的方方面面，总览如下：

Spring 框架的事务支持模型的优点

全面的事务支持是使用 Spring 框架的最有说服力的理由之一。Spring 框架为事务管理提供了一致的抽象层，并具有以下优势：

• 跨不同事务 API 的一致编程模型，如 JTA (Java Transaction API)、JPA (Java Persistence API)、JDBC、Hibernate、MyBatis。
• 支持声明式事务管理，可通过 XML 或注解进行配置。
• 比复杂的事务 API（如 JTA）更简单的编程式事务管理 API。
• 与 Spring 框架的数据访问抽象层集成。

声明式事务管理

理解声明式事务实现

关于 Spring 框架的声明式事务支持，最重要的概念是掌握其通过 AOP 代理来启用此支持，并且事务 advice 由元数据（基于 XML 或注释 @Transactional）驱动。AOP 与事务元数据的组合产生 AOP 代理，该代理使用 TransactionInterceptor 搭配合适的 PlatformTransactionManager 实现来驱动围绕方法调用的事务代理。

TransactionInterceptor 的结构如下：

下图展示了调用事务代理方法的过程：

基于 XML 方式配置事务管理

使用 <tx:advice/> 创建事务 advice，并创建切面通过 <aop:advisor/> 指定该事务 advice 须应用到哪些切点之上：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

<!-- the transactional advice (what 'happens'; see the <aop:advisor/> bean below) -->
<tx:advice id="txAdvice" transaction-manager="txManager">
    <!-- the transactional semantics... -->
    <tx:attributes>
        <!-- all methods starting with 'get' are read-only -->
        <tx:method name="get*" read-only="true"/>
        <!-- other methods use the default transaction settings (see below) -->
        <tx:method name="*"/>
    </tx:attributes>
</tx:advice>

<!-- ensure that the above transactional advice runs for any execution
        of an operation defined by the FooService interface -->
<aop:config>
    <aop:pointcut id="fooServiceOperation" expression="execution(* x.y.service.FooService.*(..))"/>
    <aop:advisor advice-ref="txAdvice" pointcut-ref="fooServiceOperation"/>
</aop:config>

......

事务配置可通过修改 <tx:method/> 的属性，详见脑图。

基于注解方式配置事务管理

除了使用基于 XML 的方式（<tx:advice/>）声明事务配置之外，您还可以使用基于注解的方式（@Transactional ）。直接在 Java 源代码中声明事务语义会使声明更靠近受影响的代码，易于配置和修改。这样之所以不存在过度耦合的原因是因为，无论如何，用于事务处理的代码几乎总是以事务的方式进行部署。

您可以将 @Transactional 注解应用于：

• 接口定义（interface）
• 接口上的方法
• 类定义（class）
• 类上的公有方法（public method on class）

@Transactional 提供的配置属性如下：

开启事务支持

但是，仅仅使用 @Transactional 注解并不足以激活事务行为，还需要开启事务支持，可以使用以下方式：

• <tx:annotation-driven/>
• @EnableTransactionManagement

配置参数如下：

更多注意点，详见官方文档：

The default advice mode for processing @Transactional annotations is proxy, which allows for interception of calls through the proxy only. Local calls within the same class cannot get intercepted that way. For a more advanced mode of interception, consider switching to aspectj mode in combination with compile-time or load-time weaving.

In proxy mode (which is the default), only external method calls coming in through the proxy are intercepted. This means that self-invocation (in effect, a method within the target object calling another method of the target object) does not lead to an actual transaction at runtime even if the invoked method is marked with @Transactional.

The proxy-target-class attribute controls what type of transactional proxies are created for classes annotated with the @Transactional annotation. If proxy-target-class is set to true, class-based proxies are created. Ifproxy-target-class is false or if the attribute is omitted, standard JDK interface-based proxies are created. (See [aop-proxying] for a discussion of the different proxy types.)

The Spring team recommends that you annotate only concrete classes (and methods of concrete classes) with the @Transactional annotation, as opposed to annotating interfaces. You certainly can place the @Transactional annotation on an interface (or an interface method), but this works only as you would expect it to if you use interface-based proxies. The fact that Java annotations are not inherited from interfaces means that, if you use class-based proxies (proxy-target-class="true") or the weaving-based aspect (mode="aspectj"), the transaction settings are not recognized by the proxying and weaving infrastructure, and the object is not wrapped in a transactional proxy.

When you use proxies, you should apply the @Transactional annotation only to methods with public visibility. If you do annotate protected, private or package-visible methods with the @Transactional annotation, no error is raised, but the annotated method does not exhibit the configured transactional settings. If you need to annotate non-public methods, consider using AspectJ.

@EnableTransactionManagement 注解主要用于导入 TransactionManagementConfigurationSelector，其判断 mode 属性：

• mode 为 AdviceMode.PROXY，返回配置 org.springframework.transaction.annotation.ProxyTransactionManagementConfiguration，该 Java Config 用于配置以下 bean：
  • TransactionInterceptor 最关键的类
  • TransactionAttributeSource
  • BeanFactoryTransactionAttributeSourceAdvisor
• mode 为 AdviceMode.ASPECTJ，默认返回配置 org.springframework.transaction.aspectj.AspectJTransactionManagementConfiguration

事务的传播行为

其中事务的传播行为需要留意下，是 Spring 特有的概念，与数据库无关。它是为了解决业务层方法之间互相调用的事务问题而引入的。当事务方法被另一个事务方法调用时，必须指定事务应该如何传播。例如：方法可能继续在现有事务中运行，也可能开启一个新事务，并在自己的事务中运行。有以下几种方式：

强烈不建议使用非事务方式执行，因此上述标注删除线的传播行为不建议使用。

在 Spring 管理的事务中，请注意物理事务和逻辑事务之间的区别，以及传播行为应用于两者之上时的区别。

REQUIRED

REQUIRED_NEW

编程式事务管理

Spring 框架提供了两种编程式事务管理方法：

• 直接使用 Spring 框架最底层的 PlatformTransactionManager 的实现类；
• 更建议使用 Spring 框架封装过的 TransactionTemplate 事务模板类。

使用 PlatformTransactionManager

Spring 事务抽象的关键在于事务策略的概念。事务策略由org.springframework.transaction.PlatformTransactionManager接口定义 ，如下所示：

1
2
3
4
5
6
7
8

public interface PlatformTransactionManager {

    TransactionStatus getTransaction(TransactionDefinition definition) throws TransactionException;

    void commit(TransactionStatus status) throws TransactionException;

    void rollback(TransactionStatus status) throws TransactionException;
}

由于 PlatformTransactionManager 是一个接口，因此很容易按需 mock 或 stub。它与查找策略无关，例如JNDI。PlatformTransactionManager 的实现就像其它对象或 bean 一样在 Spring 框架的 IoC 容器中定义。仅此优势就让 Spring 框架事务成为一种有价值的抽象，即便是使用 JTA。与直接使用 JTA 相比，您可以更轻松地测试事务代码。

同时，为了与 Spring 的理念保持一致，PlatformTransactionManager 接口的所有方法抛出的 TransactionException 异常都是非受检的（即继承自 java.lang.RuntimeException 类）。事务基础设施故障几乎都是致命性的。只有极少数情况下，应用程序能够从事务故障中恢复过来。开发人员仍然可以选择 try catch TransactionException，但重点是开发人员不会被迫这样做。

getTransaction(..) 方法根据 TransactionDefinition 参数返回一个 TransactionStatus 对象 。TransactionStatus 表示一个新事务，但如果当前调用堆栈中存在匹配事务，则表示该已有事务，即 TransactionStatus 是与执行的线程相关联的。

TransactionDefinition 接口可以控制事务的传播行为、隔离级别、超时时间、只读状态，其结构如下：

TransactionStatus 接口为事务代码提供了一种简单的方法来控制事务执行和查询事务状态，其结构如下：

在 Spring 中无论选择使用声明式还是编程式事务管理，定义正确的 PlatformTransactionManager 实现都是绝对必要的。Spring 提供了下面几种实现：

其继承关系如下：

以最常用的 DataSourceTransactionManager 为例，重点看下都提供了哪些方法：

使用 TransactionTemplate

和 Spring 框架的其它模板类一样，TransactionTemplate 也采用了回调方法来减少样板代码。相比起直接使用 PlatformTransactionManager 接口，TransactionTemplate 可以让开发人员无须重复编写获取与释放事务资源的代码，从而更聚焦于业务代码。

你需要编写一个 TransactionCallback 实现（通常为匿名内部类），其中包含需要在事务上下文中执行的代码。然后传递给 TransactionTemplate 的 execute(..) 方法去执行：

由于 TransactionTemplate 继承自 DefaultTransactionDefinition，因此可以直接修改其属性进行事务配置（如传播行为、隔离级别、超时时间等）。TransactionTemplate 类的实例是线程安全的，实例并不维护任何会话状态，但是却会维护配置状态。因此，当多个类共享使用同一个 TransactionTemplate 类的实例时，如果其中一个需要使用不同的配置（例如不同的隔离级别），你需要创建两个不同的 TransactionTemplate 类的实例。

使用示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

@Slf4j
@Service
public class TestDbService {
    
    @Autowired
    private TestDAO testDAO;
    @Autowired
    private PlatformTransactionManager transactionManager;
    @Autowired
    private TransactionTemplate transactionTemplate;

    /**
     * 要使用底层 `PlatformTransactionManager` 接口直接管理事务，请先注入所需的实现类。
     * 然后，通过 `TransactionDefinition` 和 `TransactionStatus` 对象启动、回滚和提交事务。
     */
    public Integer save() {
        DefaultTransactionDefinition def = new DefaultTransactionDefinition();
        // explicitly setting the transaction name is something that can be done only programmatically
        def.setName("SomeTxName");
        def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
        
        TransactionStatus status = transactionManager.getTransaction(def);
        try {
            Integer result = insert();
            transactionManager.commit(status);
            return result;
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            transactionManager.rollback(status);
            return 0;
        }
    }

    /**
     * TransactionTemplate 采用了回调方法来减少样板代码。
     */
    public Integer save1() {
        return transactionTemplate.execute(status -> {
            try {
                return insert();
            } catch (Exception e) {
                log.error(e.getMessage(), e);
                status.setRollbackOnly();
                return 0;
            }
        });
    }

    /**
     * 使用注解方式配置事务，是最简单最推荐的方式。事务的参数配置详见脑图。
     */
    @Transactional(rollbackFor = Exception.class)
    public Integer save3() {
        return insert();
    }

}

参考

https://docs.spring.io/spring/docs/current/spring-framework-reference/data-access.html#transaction

https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/transaction/annotation/Transactional.html

https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/transaction/annotation/Propagation.html

《@Transactional 注解失效的 3 种原因及解决方案》

《如何将 @Transactional 事务注解运用到炉火纯青？》

Spring JDBC 简介

Spring 的 JDBC 框架承担了资源管理和异常处理的工作，从而简化了底层 JDBC API 代码，让我们只需编写从数据库读写数据所需的代码。具体特性如下：

• Spring 为读取和写入数据库的几乎所有错误提供了丰富的异常，且不与特定的持久化框架相关联（如下图）。异常都继承自的父类 DataAccessException，是一个非受检异常，无需捕获，因为 Spring 认为触发异常的很多问题是不能在 catch 代码块中修复，因此不强制开发人员编写 catch 代码块。这把是否要捕获异常的权利留给了开发人员。

  

• Spring 将数据访问过程中固定的和可变的部分明确划分为两个不同的类：模板（template） 和 回调（callback）。模板管理过程中固定的部分（如事务控制、资源管理、异常处理），而回调处理自定义的数据访问代码（如 SQL 语句、绑定参数、整理结果集）。针对不同的持久化平台，Spring 提供了多个可选的模板：

  

依赖安装

要在 Spring 中使用 JDBC，需要依赖 spring-jdbc。如果使用 Spring Boot 的话，可以直接导入起步依赖 spring-boot-starter-jdbc：

1
2
3
4
5
6
7
8
9
10
11

<!-- Spring JDBC 起步依赖 -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

<!-- MySQL JDBC 驱动程序 -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

mvn dependency:tree 分析传递依赖如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

[INFO] +- mysql:mysql-connector-java:jar:8.0.13:compile
[INFO] \- org.springframework.boot:spring-boot-starter-jdbc:jar:2.1.2.RELEASE:compile
[INFO]    +- org.springframework.boot:spring-boot-starter:jar:2.1.2.RELEASE:compile
[INFO]    |  +- org.springframework.boot:spring-boot:jar:2.1.2.RELEASE:compile
[INFO]    |  |  \- org.springframework:spring-context:jar:5.1.4.RELEASE:compile
[INFO]    |  |     +- org.springframework:spring-aop:jar:5.1.4.RELEASE:compile
[INFO]    |  |     \- org.springframework:spring-expression:jar:5.1.4.RELEASE:compile
[INFO]    |  +- org.springframework.boot:spring-boot-autoconfigure:jar:2.1.2.RELEASE:compile
[INFO]    |  +- org.springframework.boot:spring-boot-starter-logging:jar:2.1.2.RELEASE:compile
[INFO]    |  |  +- ch.qos.logback:logback-classic:jar:1.2.3:compile
[INFO]    |  |  |  \- ch.qos.logback:logback-core:jar:1.2.3:compile
[INFO]    |  |  +- org.apache.logging.log4j:log4j-to-slf4j:jar:2.11.1:compile
[INFO]    |  |  |  \- org.apache.logging.log4j:log4j-api:jar:2.11.1:compile
[INFO]    |  |  \- org.slf4j:jul-to-slf4j:jar:1.7.25:compile
[INFO]    |  +- javax.annotation:javax.annotation-api:jar:1.3.2:compile
[INFO]    |  +- org.springframework:spring-core:jar:5.1.4.RELEASE:compile
[INFO]    |  |  \- org.springframework:spring-jcl:jar:5.1.4.RELEASE:compile
[INFO]    |  \- org.yaml:snakeyaml:jar:1.23:runtime
[INFO]    +- com.zaxxer:HikariCP:jar:3.2.0:compile
[INFO]    |  \- org.slf4j:slf4j-api:jar:1.7.25:compile
[INFO]    \- org.springframework:spring-jdbc:jar:5.1.4.RELEASE:compile
[INFO]       +- org.springframework:spring-beans:jar:5.1.4.RELEASE:compile
[INFO]       \- org.springframework:spring-tx:jar:5.1.4.RELEASE:compile

可见，spring-boot-starter-jdbc 引入了如下传递依赖：

• spring-boot-starter
  • spring-boot-autoconfigure Spring Boot 自动配置类
• spring-jdbc Spring JDBC 核心库
• HikariCP，Spring Boot 2 的默认数据库连接池
• ……

配置解析

spring-boot-autoconfigure 依赖内含几个关键的配置类，提供了如下外部配置：

• org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration，用于自动配置嵌入式数据源 或 连接池数据源

  1 2 3 4 5 6

  spring: datasource: driver-class-name: url: username: password:

• org.springframework.boot.autoconfigure.jdbc.JndiDataSourceAutoConfiguration，用于自动配置 JNDI 数据源

  1 2 3

  spring: datasource: jndi-name:

• org.springframework.boot.autoconfigure.jdbc.XADataSourceAutoConfiguration，用于自动配置分布式事务的数据源

  1 2 3 4 5

  spring: datasource: xa: data-source-class-name: properties:

• org.springframework.boot.autoconfigure.jdbc.JdbcTemplateAutoConfiguration，用于导入配置类：JdbcTemplateConfiguration、NamedParameterJdbcTemplateConfiguration

  1 2 3 4 5 6

  spring: jdbc: template: fetch-size: max-rows: query-timeout:

• org.springframework.boot.autoconfigure.jdbc.DataSourceTransactionManagerAutoConfiguration 用于自动配置 DataSourceTransactionManager

• org.springframework.boot.autoconfigure.transaction.TransactionAutoConfiguration

  1 2 3 4

  spring: transaction: defaultTimeout: rollbackOnCommitFailure:

使用 JDBC Template

配置数据源

为了让 JdbcTemplate 正常工作，只需要为其设置 DataSource 数据源即可。Spring Boot 下直接使用外部配置：

1
2
3
4
5
6
7

spring:
  datasource:
    type: com.zaxxer.hikari.HikariDataSource  #使用 HikariCP，Spring Boot 2 的默认数据库连接池
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/test?useUnicode=true;characterEncoding=utf-8
    username: 
    password: 

如果未使用 Spring Boot，Java Config 如下：

1
2
3
4
5
6
7
8
9

@Bean
public DataSource dataSource() {
    HikariDataSource ds = new HikariDataSource();
    ds.setDriverClassName("com.mysql.cj.jdbc.Driver");
    ds.setJdbcUrl("jdbc:mysql://localhost:3306/test?useUnicode=true;characterEncoding=utf-8");
    ds.setUsername("");
    ds.setPassword("");
    return ds;
}

API 介绍

• org.springframework.jdbc.core.JdbcOperations 是 Spring 封装 JDBC 操作的核心接口，提供的方法如下，基于索引参数进行 SQL 参数绑定。实现类为 org.springframework.jdbc.core.JdbcTemplate：

  1 2 3 4 5 6 7 8 9 10 11 12

  <T> T execute(...) <T> List<T> query(String, RowMapper<T>, Object...) // 多列查询 Map<String, Object> queryForMap(String, Object...) // 单行多列查询 <T> T queryForObject(String, Class<T>, Object...) // 单行单列查询 <T> T queryForObject(String, RowMapper<T>, Object...) // 单行多列查询 <T> List<T> queryForList(String, Class<T>, Object...) // 多行单列查询 List<Map<String, Object>> queryForList(String, Object...) // 多行多列查询 SqlRowSet queryForRowSet(...) int update(...) // 执行单个增删改 int[] batchUpdate(...) // 执行批量增删改 Map<String, Object> call(...) // 执行存储过程和函数 ......

• org.springframework.jdbc.core.namedparam.NamedParameterJdbcOperations 接口支持将值以命名参数的形式绑定到 SQL，实现类为 org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate，其底层使用的仍然是 JdbcOperations，是一个二次封装的 API，推荐使用。

如果使用 Spring Boot 的话，可以直接导入起步依赖 spring-boot-starter-jdbc，会引入自动配置类 org.springframework.boot.autoconfigure.jdbc.JdbcTemplateAutoConfiguration，用于导入配置类：JdbcTemplateConfiguration、NamedParameterJdbcTemplateConfiguration，源码如下：

1
2
3
4
5
6
7
8
9

@Configuration(proxyBeanMethods = false)
@ConditionalOnClass({ DataSource.class, JdbcTemplate.class })
@ConditionalOnSingleCandidate(DataSource.class)
@AutoConfigureAfter(DataSourceAutoConfiguration.class)
@EnableConfigurationProperties(JdbcProperties.class)
@Import({ JdbcTemplateConfiguration.class, NamedParameterJdbcTemplateConfiguration.class })
public class JdbcTemplateAutoConfiguration {

}

只要满足几个条件，该自动配置类就会生效：

• classpath 包含 DataSource、JdbcTemplate 类
• DataSource bean 有且只有一个

JdbcOperations

依赖注入 JdbcTemplate 实现之后，使用如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

List<TestPO> testPOList = jdbcOperations.query(
        "SELECT id, name, city FROM test WHERE name = ? AND city = ?", 
        (rs, rowNum) -> new TestPO(
            rs.getLong("id"),
            rs.getString("name"),
            rs.getString("city")
        ), 
        "李四", "beijing"
    );
log.info("Result is {}", testPOList);  // Result is [TestPO(id=2, name=李四, city=beijing)]

TestPO testPO = jdbcOperations.queryForObject(
        "SELECT id, name, city FROM test WHERE id = ?", 
        (rs, rowNum) -> new TestPO(
            rs.getLong("id"),
            rs.getString("name"),
            rs.getString("city")
        ), 
        2
    );
log.info("Result is {}", testPO);  // Result is TestPO(id=2, name=李四, city=beijing)

String name = jdbcOperations.queryForObject("SELECT name FROM test WHERE id = ?", String.class, 2);
log.info("Result is {}", name);  // Result is 李四

List<String> names = jdbcOperations.queryForList("SELECT name FROM test WHERE city = ?", String.class, "beijing");
log.info("Result is {}", names);  // Result is [李四, 王五]

List<Map<String, Object>> testMapList = jdbcOperations.queryForList("SELECT id, name, city FROM test WHERE city = ?", "beijing");
log.info("Result is {}", testMapList);  // Result is [{id=2, name=李四, city=beijing}, {id=3, name=王五, city=beijing}]

Map<String, Object> testMap = jdbcOperations.queryForMap("SELECT id, name, city FROM test WHERE id = ?", 2);
log.info("Result is {}", testMap);  // Result is {id=2, name=李四, city=beijing}

NamedParameterJdbcTemplate

使用 JdbcOperations 需要特别注意索引参数的正确顺序，如果在修改 SQL 时忘记修改参数顺序，将导致查询出错。因此更建议使用命名参数，按照名字来绑定值：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

Map<String, Object> cityParamMap = new HashMap<>();
paramMap.put("city", "beijing");
paramMap.put("name", "李四");

List<TestPO> testPOList = namedParameterJdbcOperations.query(
        "SELECT id, name, city FROM test WHERE name = :name AND city = :city", 
        paramMap, 
        (rs, rowNum) -> new TestPO(
            rs.getLong("id"),
            rs.getString("name"),
            rs.getString("city")
        )
    );

log.info("Result is {}", testPOList);  // Result is [TestPO(id=2, name=李四, city=beijing)]

由于 SQL 中的数据类型和 Java 编程语言中的数据类型并不相同，因此需要使用某种机制在使用 Java 类型的应用程序和使用 SQL 类型的数据库之间传输数据。

SQL 类型映射到 Java 类型

不同数据库产品支持的 SQL 类型之间存在显着差异。即使不同的数据库支持具有相同语义的 SQL 类型，它们也可能为这些类型提供了不同的名称。例如，大多数主要数据库都支持 large binary 这种 SQL 类型，但是：

• MySQL 的命名为 BINARY、VARBINARY（详见：The BINARY and VARBINARY Types）
• Oracle 的命名为 LONG RAW
• Sybase 的命名为 IMAGE
• Informix 的命名为 BYTE
• DB2 的命名为 LONG VARCHAR FOR BIT DATA

幸运的是，JDBC 开发通常不需要关心目标数据库使用的实际 SQL 类型名称。大多数情况下，JDBC 开发将针对现有数据库表进行编程，并不需要关心用于创建这些表的确切 SQL 类型名称。

JDBC API 在 java.sql.Types 类中定义了一组通用 SQL 类型标识符，旨在表达最常用的 SQL 类型。在使用 JDBC API 进行编程时，程序员通常可以使用这些 JDBC 类型来引用通用 SQL 类型，而无需关心目标数据库使用的确切 SQL 类型名称。

数据访问 API

为了在数据库和 Java 应用程序之间传输数据，JDBC API 提供了三组方法：

• PreparedStatement 类提供的用于将 Java 类型作为 SQL 语句参数发送的方法；
• ResultSet 类提供的用于将 SELECT 检索结果转换为 Java 类型的方法；
• CallableStatement类提供的用于将 OUT 参数转换为 Java 类型的方法。

静态数据访问

Java 程序从数据库中检索数据时，都必然会有某种形式的数据映射和数据转换。大多数情况下，JDBC 开发是知道目标数据库的 schema 的，例如表结构及其每列的数据类型。因此，JDBC 开发可以使用 ResultSet、PreparedStatement、CallableStatement 接口的强类型访问方法进行类型转换，如下：

• PreparedStatement 接口：

  1 2 3 4

  void setBoolean(int parameterIndex, boolean x) throws SQLException; void setByte(int parameterIndex, byte x) throws SQLException; void setInt(int parameterIndex, int x) throws SQLException; ...

• ResultSet 接口：

  1 2 3 4 5 6 7

  boolean getBoolean(int columnIndex) throws SQLException; boolean getBoolean(String columnLabel) throws SQLException; byte getByte(int columnIndex) throws SQLException; byte getByte(String columnLabel) throws SQLException; int getInt(int columnIndex) throws SQLException; int getInt(String columnLabel) throws SQLException; ...

动态数据访问

在大多数情况下，用户都希望访问在编译期数据类型已知的结果或参数。但是某些情况下，应用程序在编译期无法获知它们访问的目标数据库的 schema。因此，除了静态的数据类型访问之外，JDBC 还提供了对动态的数据类型访问的支持。

访问在编译期数据类型未知的值，可以使用所有 Java 对象的共同父类 Object 类型：

• PreparedStatement 接口：

  1 2	void setObject(int parameterIndex, Object x) throws SQLException; void setObject(int parameterIndex, Object x, int targetSqlType) throws SQLException;

• ResultSet 接口：

  1 2 3 4 5 6 7 8 9

  Object getObject(int columnIndex) throws SQLException; Object getObject(String columnLabel) throws SQLException; Object getObject(int columnIndex, java.util.Map<String,Class<?>> map) throws SQLException; Object getObject(String columnLabel, java.util.Map<String,Class<?>> map) throws SQLException; // ... will convert from the SQL type of the column to the requested Java data type, if the conversion is supported. If the conversion is not supported or null is specified for the type, a `SQLException` is thrown. <T> T getObject(int columnIndex, Class<T> type) throws SQLException; <T> T getObject(String columnLabel, Class<T> type) throws SQLException;

  ⚠️ 特别注意：对于最后一组动态数据访问方法，参数二 type 的值要与 ResultSetMetaData.GetColumnClassName() 返回的类型相匹配，类型转换才能成功。否则抛出异常如下：

  

  例如 MyBatis Plus com.baomidou.mybatisplus.core.handlers.MybatisEnumTypeHandler 就使用到了 ResultSet#getObject 方法，如果类型转换失败则报错如上。

  关于 MySQL 类型与 Java 类型的映射关系，参考：https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-type-conversions.html

示例代码如下：

1
2
3
4
5
6
7
8
9
10
11
12

try (Connection conn = DriverManager.getConnection(url)) {
    try (PreparedStatement stmt = conn.prepareStatement("SELECT * FROM test WHERE name = ?;")) {
        stmt.setObject(1, "李四", JDBCType.VARCHAR);
        try (ResultSet rs = stmt.executeQuery()) {
            while (rs.next()) {
                Object id = rs.getObject("id");
                String name = rs.getObject("name", String.class);
                log.info("Result is {}, {}", id instanceof Long, name);  // Result is true, 李四
            }
        }
    }
}

boolean, char, byte, short, int, long, float, double 八种基本数据类型将返回其对应的包装类型，其它的则返回对应的类型。

设置 NULL 值

Some databases need to know the value’s type even if the value itself is NULL. For this reason, for maximum portability, it’s the JDBC specification itself that requires the java.sql.Types to be specified:

• PreparedStatement 接口：

  1 2 3

  // Sets the designated parameter to SQL NULL. void setNull(int parameterIndex, int sqlType) throws SQLException; void setNull(int parameterIndex, int sqlType, String typeName) throws SQLException;

参考：Is JdbcType necessary in a MyBatis mapper?

参考

https://docs.oracle.com/javase/6/docs/technotes/guides/jdbc/getstart/mapping.html

java.sql.Types

• https://github.com/qidawu/java-api-test/blob/master/src/test/java/jdbc/DynamicTypeTest.java
• Java Examples for java.sql.Types
• org.apache.ibatis.type.JdbcType
• com.baomidou.mybatisplus.core.handlers.MybatisEnumTypeHandler

总览

首先，来总览下 JDBC API：

JDBC API 规范

JDBC API 作为 Java SE™（Java 标准版）的一部分，由以下部分组成：

• JDBC 核心 API —— java.sql package。

• JDBC 可选 API —— javax.sql package，是 Java EE™（Java 企业版）的重要组成部分。

其中，java.sql package 包含下列 API：

• 通过 java.sql.DriverManager 与数据库建立连接

  • java.sql.DriverManager 类 - 用于与驱动程序建立连接
  • java.sql.SQLPermission 类
  • java.sql.Driver 接口 - 提供用于注册和连接驱动程序的 API。
  • java.sql.DriverPropertyInfo 类 - 提供 JDBC 驱动程序的属性。

• 发送 SQL 语句到数据库

  • java.sql.Connection 接口 - 提供创建语句、管理连接及其属性的方法
  • java.sql.Statement 接口 - 用于发送基本的 SQL 语句
  • java.sql.PreparedStatement 接口 - 用于发送预编译语句或基本 SQL 语句（继承自Statement）
  • java.sql.CallableStatement 接口 - 用于调用数据库存储过程（继承自PreparedStatement）
  • java.sql.Savepoint 接口 - 在事务中提供保存点

• 检索和更新查询结果

  • java.sql.ResultSet 接口

• 标准映射（SQL 数据类型到 Java 类或接口）

• 自定义映射（SQL user-defined type (UDT) 到 Java 类）

• 元数据

  • java.sql.DatabaseMetaData 接口 - 提供有关数据库的信息
  • java.sql.ResultSetMetaData 接口 - 提供有关 ResultSet 对象的列信息
  • java.sql.ParameterMetaData 接口 - 提供有关 PreparedStatement 命令的参数信息

• 异常

  • java.sql.SQLException 类 - 被大多数方法抛出，当数据访问出现问题或出于其它原因
  • java.sql.SQLWarning 类 - 抛出表示警告
  • java.sql.DataTruncation 类 - 抛出表示数据可能已被截断
  • java.sql.BatchUpdateException 类 - 抛出表示批量更新中的部分命令未执行成功

下面重点看下常用的接口和类。

DriverManager 类

java.sql.DriverManager 类充当用户和驱动程序之间的接口。它跟踪可用的驱动程序并处理数据库与相应驱动程序之间的连接。DriverManager 类维护了一个通过调用 DriverManager.registerDriver() 方法来注册自己的 java.sql.Driver 类列表。

常用方法：

1
2
3
4

static void registerDriver(Driver driver) // 用于通过 `DriverManager` 注册给定的驱动程序。
static void deregisterDriver(Driver driver)  // 用于从 `DriverManager` 取消注册给定的驱动程序（从列表中删除驱动程序）。
static Connection getConnection(String url)  // 用于与指定的 URL 建立连接。
static Connection getConnection(String url, String userName, String password)  // 用于与指定的 URL 建立连接，通过用户名和密码。

关于 Driver 驱动程序注册，详见《注册驱动程序》。

Connection 接口

java.sql.Connection 接口表示 Java 应用程序和数据库之间的会话（Session），它提供了许多事务管理方法如：

1
2
3
4
5
6
7
8
9
10
11
12

void setAutoCommit(boolean status)  // 修改当前 `Connection` 对象的事务自动提交模式。默认为 `true`。
void setReadOnly(boolean readOnly)  // 修改当前 `Connection` 对象的只读状态以提示驱动程序开启数据库优化。
void setTransactionIsolation(int level)  // 修改当前 `Connection` 对象的事务隔离级别。
void commit()  // 保存自上次提交/回滚以来所做的所有更改。
void rollback()  // 丢弃自上次提交/回滚以来所做的所有更改。

// 在当前事务中设置或移除保存点。
Savepoint setSavepoint()
Savepoint setSavepoint(String name)
void releaseSavepoint(Savepoint savepoint)

void close()  // 关闭连接并立即释放 JDBC 资源。

Connection 接口同时也是一个工厂类，用于获取 Statement、PreparedStatement 和 DatabaseMetaData 对象：

1
2
3
4

Statement createStatement(...)  // 创建一个可用于执行 SQL 查询或更新的语句对象。
PreparedStatement prepareStatement(...)  // 创建一个可用于执行 SQL 参数化查询或更新的语句对象。
CallableStatement prepareCall(...)  // 用于调用存储过程和函数。
DatabaseMetaData getMetaData()  // 用于获取数据库的元数据，例如数据库产品名称，数据库产品版本，驱动程序名称，表总数名称，总视图名称等。

Statement 接口

java.sql.Statement 接口提供用于执行数据库查询与更新的方法。Statement 接口是 ResultSet 的工厂，即它提供工厂方法来获取 ResultSet 的对象。

1
2
3

ResultSet executeQuery(String sql)  // 用于执行 `SELECT` 查询并返回 `ResultSet` 的对象。
int executeUpdate(String sql)  // 用于执行指定的更新，如 `create`，`drop`，`insert`，`update`，`delete` 等。
boolean execute(String sql)  // 用于执行可能返回多种结果的查询。

批处理

除了通过上述方法来执行单个查询或更新，还可以通过下列方法执行批量命令：

1
2
3

void addBatch(String sql)
void clearBatch()
int[] executeBatch()

使用批量命令前，记得先使用 setAutoCommit() 将事务的自动提交模式设置为 false 。

批处理允许您将相关的 SQL 语句分组到批处理中，并通过一次调用数据库来提交它们。当您一次性向数据库发送多个 SQL 语句时，可以减少通信开销，从而提高性能。参考：JDBC - Batch Processing。

PreparedStatement 接口

java.sql.PreparedStatement 接口是 java.sql.Statement 的子接口。它用于执行参数化查询（parameterized query），例如：

1

PreparedStatement ps = connection.prepareStatement("insert into emp values(?, ?, ?)");

为什么要使用 PreparedStatement？

• 提升性能：应用程序的性能会更快，因为 SQL 语句只会编译一次。
• 提升安全：预防 SQL 注入

创建预编译的参数化查询语句后，需要通过 setXxx 方法设置对应参数。参数设置完毕后，就可以通过下列方法执行 SQL 语句：

1
2
3

ResultSet executeQuery()  // 用于执行 `SELECT` 查询并返回 `ResultSet` 的对象。
int executeUpdate()  // 用于执行指定的更新，如 `create`，`drop`，`insert`，`update`，`delete` 等。
boolean execute()  // 用于执行可能返回多种结果的查询。

主键回写

java.sql.Connection 创建 java.sql.PreparedStatement 时，允许通过 autoGeneratedKeys 指定是否返回自增主键：

1

PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException;

autoGeneratedKeys - a flag indicating whether auto-generated keys should be returned; one of Statement.RETURN_GENERATED_KEYS or Statement.NO_GENERATED_KEYS

1
2
3
4
5
6
7
8
9
10
11
12
13

/**
 * The constant indicating that generated keys should be made available for retrieval.
 *
 * @since 1.4
 */
int RETURN_GENERATED_KEYS = 1;

/**
 * The constant indicating that generated keys should not be made available for retrieval.
 *
 * @since 1.4
 */
int NO_GENERATED_KEYS = 2;

例子：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

// 构造 PreparedStatement 时，多了第二个参数，指定了需要主键回写
PreparedStatement ps = connection.prepareStatement("insert into emp values(?, ?, ?)", 
                                                   Statement.RETURN_GENERATED_KEYS);
ps.setString(name);
ps.setLong(id);
ps.setInt(salary);
ps.executeUpdate();

// 调用 getGeneratedKeys ，然后又会获取到一个 ResultSet 对象，从这个游标中就可以获取到刚刚插入数据的 id
ResultSet rs = ps.getGeneratedKeys();
int id = 0;
if (rs.next()) {
    id = rs.getInt(1);
}
return id;

批处理

PreparedStatement 还提供了批处理方式，减少网络请求，提升性能，API 如下：

1
2
3
4
5
6
7
8
9
10

/**
 * Adds a set of parameters to this <code>PreparedStatement</code>
 * object's batch of commands.
 *
 * @exception SQLException if a database access error occurs or
 * this method is called on a closed <code>PreparedStatement</code>
 * @see Statement#addBatch
 * @since 1.2
 */
void addBatch() throws SQLException;

未使用批处理方法：

1
2
3
4
5
6
7
8
9

PreparedStatement ps = conn.prepareStatement("INSERT into employees values (?, ?, ?)");

for (n = 0; n < 100; n++) {
    ps.setString(name[n]);
    ps.setLong(id[n]);
    ps.setInt(salary[n]);
    // 多次执行PreparedStatement，多次数据库请求（网络请求）
    ps.executeUpdate();  
}

使用批处理方法，一次性执行多条 SQL：

1
2
3
4
5
6
7
8
9
10
11

PreparedStatement ps = conn.prepareStatement("INSERT into employees values (?, ?, ?)");
  
for (n = 0; n < 100; n++) {
    ps.setString(name[n]);
    ps.setLong(id[n]);
    ps.setInt(salary[n]);
    // 添加批次
    ps.addBatch();
}
// 调用父接口 Statement#executeBatch() 执行批次
ps.executeBatch();

ResultSet 接口

java.sql.ResultSet 对象维护了一个指向 table 行的游标。游标初始值指向第 0 行。默认情况下，ResultSet 对象只能向前移动，并且不可更新。可以通过在 createStatement(int, int) 方法中传递指定参数修改该默认行为。

可以通过以下方法操作游标：

1
2
3
4
5
6

boolean next()  // 将游标移动到当前位置的下一行。
boolean previous()  // 将游标移动到当前位置之前的一行。
boolean first()  // 将游标移动到结果集的第一行。
boolean last()  // 将游标移动到结果集的最后一行。
boolean absolute(int row)  // 将游标移动到结果集的指定行号。
boolean relative(int row)  // 将游标移动到结果集的相对行号，它可以是正数或负数。

将游标移动到指定行之后，可以通过 getXxx 方法获取当前行的指定列的数据。

此外，还可以直接获取 table 的元数据，例如列的总数，列名，列类型等：

1

ResultSetMetaData getMetaData()

ResultSetMetaData 接口

java.sql.ResultSetMetaData 用于获取 table 的元数据，例如列的总数，列名，列类型等。

DatabaseMetaData 接口

java.sql.DatabaseMetaData 用于获取数据库的元数据，例如数据库产品名称，数据库产品版本，驱动程序名称，表总数名称，总视图名称等。

RowSet 接口

javax.sql.RowSet 继承自 java.sql.ResultSet，是其包装器类。它包含类似 ResultSet 的表格数据，但使用起来非常简单灵活。其实现类如下：

下面是一个不含事件处理代码的 JdbcRowSet 的简单示例：

1
2
3
4
5
6
7
8
9
10

try (JdbcRowSet rowSet = RowSetProvider.newFactory().createJdbcRowSet()) {
    rowSet.setUrl(url);
    rowSet.setCommand("SELECT * FROM test");
    rowSet.execute();
    while (rowSet.next()) {
        int id = rowSet.getInt("id");
        String name = rowSet.getString("name");
        log.info("Result is {} {}", id, name);
    }
}

对比下面传统的 JDBC API，代码更加直观，需要直接管理的资源也更少：

1
2
3
4
5
6
7
8
9
10
11

try (Connection conn = DriverManager.getConnection(url)) {
    try (Statement stmt = conn.createStatement()) {
        try (ResultSet rs = stmt.executeQuery("SELECT * FROM test")) {
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                log.info("Result is {}, {}", id, name);
            }
        }
    }
}

要使用 JdbcRowSet 执行事件处理，需要在 JdbcRowSet 的 addRowSetListener 方法中添加 RowSetListener 的实例。RowSetListener 接口提供了必须实现的三个方法，如下：

1
2
3

void cursorMoved(RowSetEvent event);
void rowChanged(RowSetEvent event);
void rowSetChanged(RowSetEvent event);

DataSource 接口

JDBC API 示例

JDBC API 的使用步骤如下：

其中：

1. 步骤一：JDBC API 从 4.0 开始利用 Java SPI 机制自动加载驱动程序，可以省略该步骤。
2. 步骤二、三：如果使用如 Spring JdbcTempate、MyBatis 等框架，可以省略该步骤。
3. 步骤五：使用 try-with-resources 语句，可以省略该步骤。

下面来两个示例：

存储图片

下例通过 PreparedStatement 接口的 setBinaryStream() 方法将图片（二进制信息）存储到数据库中。为了将图片存储到数据库中，需要在表中使用 BLOB（Binary Large Object）数据类型。

1
2
3
4
5
6
7
8

try (Connection conn = DriverManager.getConnection(url)) {
    try (PreparedStatement stmt = conn.prepareStatement("INSERT INTO test(title, photo) VALUES(?, ?)")) {
        FileInputStream fileInputStream = new FileInputStream("F:\\test.jpg");
        stmt.setString(1, "pic1");
        stmt.setBinaryStream(2, fileInputStream);
        assertTrue(1 == stmt.executeUpdate());
    }
}

注意：这只是一个例子，生产环境中是不会将这类二进制信息存储到数据库中的，而是存储到专门的文件系统，以提升性能，并节省宝贵的数据库资源 :)

检索图片

Using try-with-resources Statements to Automatically Close JDBC Resources:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

try (Connection conn = DriverManager.getConnection(url)) {
    try (PreparedStatement stmt = conn.prepareStatement("SELECT title, photo FROM test")) {
        try (ResultSet rs = stmt.executeQuery()) {
            while (rs.next()) {
                String title = rs.getString(1);
                log.info("title is {}", title);

                Blob photo = rs.getBlob(2);
                byte[] bytes = photo.getBytes(1, (int) photo.length());
                String fileName = String.format("F:\\%s.png", title);
                try (FileOutputStream fileOutputStream = new FileOutputStream(fileName)) {
                    fileOutputStream.write(bytes);
                }
            }
        }
    }
}

JDBC 4.1 (Java SE 7) introduces the ability to use a try-with-resources statement to automatically close java.sql.Connection, java.sql.Statement, and java.sql.ResultSet objects, regardless of whether a SQLException or any other exception has been thrown. See The try-with-resources Statement for more information.

参考

《Getting Started with the JDBC API》

https://docs.oracle.com/javase/8/docs/technotes/guides/jdbc/jdbc_41.html

https://docs.oracle.com/javase/8/docs/technotes/guides/jdbc/jdbc_42.html

https://docs.oracle.com/javase/9/docs/api/java/sql/package-summary.html

https://www.javatpoint.com/java-jdbc

https://www.tutorialspoint.com/jdbc/index.htm

https://www.tutorialspoint.com/dbutils/index.htm

总览

什么是 JDBC？

JDBC 表示 Java Database Connectivity，是 Java SE（Java 标准版）的一部分。JDBC API 用于在 Java 应用程序中执行以下活动：

1. 连接到数据库
2. 执行查询并将语句更新到数据库
3. 检索从数据库收到的结果

为什么要使用 JDBC？

在 JDBC 之前，ODBC API 是用于连接和执行命令的数据库 API 标准。但是，ODBC API 是使用 C 语言编写的驱动程序，依赖于平台。这就是为什么 Java 定义了自己的 JDBC API，它使用的 JDBC 驱动程序，是用 Java 语言编写的，具有与平台无关的特性，支持跨平台部署，性能也较好。

什么是 JDBC 驱动程序？

由于 JDBC API 只是一套接口规范，因此要使用 JDBC API 操作数据库，首先需要选择合适的驱动程序：

驱动程序四种类型

有四种类型的 JDBC 驱动程序：

1. JDBC-ODBC bridge driver (In Java 8, the JDBC-ODBC Bridge has been removed.)
2. Native-API driver (partially java driver)
3. Network-Protocol driver (Middleware driver, fully java driver)
4. **Database-Protocol driver (Thin driver, fully java driver)**，目前最常用的驱动类型，日常开发中使用的驱动 jar 包基本都属于这种类型，通常由数据库厂商直接提供，例如 mysql-connector-java。驱动程序把 JDBC 调用直接转换为数据库特定的网络协议，因此性能更好。驱动程序纯 Java 实现，支持跨平台部署。

我们知道，ODBC几乎能在所有平台上连接几乎所有的数据库。为什么 Java 不使用 ODBC？

答案是：Java 可以使用 ODBC，但最好是以JDBC-ODBC桥的形式使用（Java连接总体分为Java直连和JDBC-ODBC桥两种形式）。

那为什么还需要 JDBC？

因为ODBC 不适合直接在 Java 中使用，因为它使用 C 语言接口。从Java 调用本地 C代码在安全性、实现、坚固性和程序的自动移植性方面都有许多缺点。从 ODBC C API 到 Java API 的字面翻译是不可取的。例如，Java 没有指针，而 ODBC 却对指针用得很广泛（包括很容易出错的指针”void *”）。

另外，ODBC 比较复杂，而JDBC 尽量保证简单功能的简便性，同时在必要时允许使用高级功能。如果使用ODBC，就必须手动地将 ODBC 驱动程序管理器和驱动程序安装在每台客户机上。如果完全用 Java 编写 JDBC 驱动程序则 JDBC代码在所有 Java 平台上（从网络计算机到大型机）都可以自 动安装、移植并保证安全性。

总之，JDBC 在很大程度上是借鉴了ODBC的，从他的基础上发展而来。JDBC 保留了 ODBC 的基本设计特征，因此，熟悉 ODBC 的程序员将发现 JDBC 很容易使用。它们之间最大的区别在于：JDBC 以 Java 风格与优点为基础并进行优化，因此更加易于使用。

各类型的优缺点详见：

https://en.wikipedia.org/wiki/JDBC_driver

https://www.javatpoint.com/jdbc-driver

https://blog.csdn.net/autfish/article/details/52170053

驱动程序厂商实现

如果选定使用推荐的第四种驱动程序类型，接下来需要下载对应厂商的驱动程序，目前提供这些支持列表。

MySQL Connector/J

例如最常用的 MySQL 数据库，提供了 MySQL Connector/J（即 mysql-connector-java）。Maven 依赖配置如下：

1
2
3
4
5
6

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>x.x.x</version>
</dependency>

关于 MySQL 驱动程序的更多信息，详见：https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-overview.html

• 驱动程序/数据源类名
• 连接 URL 语法
• 配置属性
• JDBC API 实现说明
• Java，JDBC 和 MySQL 类型
• 使用字符集和 Unicode
• 各种连接方式（如 SSL 安全连接）
• MySQL 错误码与 JDBC SQLState 代码的映射关系
• ……

如何使用 JDBC？

配置 JDBC URL

JDBC URL 提供了一种标识数据源的方法，以便相应的驱动程序识别它并与之建立连接。

因此，首先需要先配置好 JDBC URL，以便驱动程序注册完毕之后通过 JDBC URL 与数据源建立连接。

JDBC URL 的标准语法如下：

1

protocol//[hosts][/database][?properties]

详见：https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-jdbc-url-format.html

例如：jdbc:mysql://localhost:3306/test?useUnicode=true;characterEncoding=utf-8

常用协议

常见的 JDBC URL 协议及对应 Driver Class 如下：

配置属性

MySQL Connector/J：

https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-configuration-properties.html

https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-configuration-properties.html

useSSL

javax.net.ssl.SSLHandshakeException: No appropriate protocol (protocol is disabled or cipher suites are inappropriate)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

Caught while disconnecting...

** BEGIN NESTED EXCEPTION ** 

javax.net.ssl.SSLException
MESSAGE: closing inbound before receiving peer's close_notify

STACKTRACE:

javax.net.ssl.SSLException: closing inbound before receiving peer's close_notify
	at sun.security.ssl.Alert.createSSLException(Alert.java:133)
	at sun.security.ssl.Alert.createSSLException(Alert.java:117)
	at sun.security.ssl.TransportContext.fatal(TransportContext.java:340)
	at sun.security.ssl.TransportContext.fatal(TransportContext.java:296)
	at sun.security.ssl.TransportContext.fatal(TransportContext.java:287)
	at sun.security.ssl.SSLSocketImpl.shutdownInput(SSLSocketImpl.java:737)
	at sun.security.ssl.SSLSocketImpl.shutdownInput(SSLSocketImpl.java:716)
	at com.mysql.cj.protocol.a.NativeProtocol.quit(NativeProtocol.java:1319)
	at com.mysql.cj.NativeSession.quit(NativeSession.java:182)
	at com.mysql.cj.jdbc.ConnectionImpl.realClose(ConnectionImpl.java:1750)
	at com.mysql.cj.jdbc.ConnectionImpl.close(ConnectionImpl.java:720)
	at com.zaxxer.hikari.pool.PoolBase.quietlyCloseConnection(PoolBase.java:135)
	at com.zaxxer.hikari.pool.HikariPool.lambda$closeConnection$1(HikariPool.java:441)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at java.lang.Thread.run(Thread.java:748)


** END NESTED EXCEPTION **

解决方法一：useSSL=false

解决方法二：注释掉 java.security 文件中的 jdk.tls.disabledAlgorithms 配置：

1
2
3
4
5

$ vim ~/.sdkman/candidates/java/8.0.362-zulu/zulu-8.jdk/Contents/Home/jre/lib/security/java.security

# jdk.tls.disabledAlgorithms=SSLv3, TLSv1, TLSv1.1, RC4, DES, MD5withRSA, \
#     DH keySize < 1024, EC keySize < 224, 3DES_EDE_CBC, anon, NULL, \
#     include jdk.disabled.namedCurves

connectionTimeZone

https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-connp-props-datetime-types-processing.html#cj-conn-prop_connectionTimeZone

Setting the MySQL JDBC Timezone Using Spring Boot Configuration

useServerPrepStmts

https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-connp-props-prepared-statements.html#cj-conn-prop_useServerPrepStmts

Use server-side prepared statements if the server supports them?

MySQL 是否默认开启预编译，与 MySQL Server 的版本无关，而与 MySQL Connector/J（驱动程序）的版本有关，Connector/J 5.0.5 之前的版本默认开启预编译。Connector/J 5.0.5 及以后的版本默认不开启预编译，想启用 MySQL 预编译，就必须设置 useServerPrepStmts=true。

参考：《JDBC 的 PreparedStatement 预编译详解》

allowMultiQueries

https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-connp-props-security.html#cj-conn-prop_allowMultiQueries

Allow the use of ; to delimit multiple queries during one statement (true/false). Default is false, and it does not affect the addBatch() and executeBatch() methods, which rely on rewriteBatchedStatements instead.

基于安全考虑，默认情况下，MySQL Connector/J 禁用 ; 拼接 SQL。

如果想通过 MyBatis foreach 使用 ; 拼接 UPDATE/DELETE 语句进行批量提交（但强烈不建议），需要设置 useServerPrepStmts=true。

rewriteBatchedStatements

https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-connp-props-performance-extensions.html#cj-conn-prop_rewriteBatchedStatements

Should the driver use multi-queries (regardless of the setting of allowMultiQueries) as well as rewriting of prepared statements for INSERT into multi-value inserts when executeBatch() is called?

Notice that this has the potential for SQL injection if using plain java.sql.Statement and your code doesn’t sanitize input correctly.

Notice that for prepared statements, server-side prepared statements can not currently take advantage of this rewrite option, and that if you don’t specify stream lengths when using PreparedStatement.set*Stream(), the driver won’t be able to determine the optimum number of parameters per batch and you might receive an error from the driver that the resultant packet is too large.

Statement.getGeneratedKeys() for these rewritten statements only works when the entire batch includes INSERT statements.

Please be aware using rewriteBatchedStatements=true with INSERT .. ON DUPLICATE KEY UPDATE that for rewritten statement server returns only one value as sum of all affected (or found) rows in batch and it isn’t possible to map it correctly to initial statements; in this case driver returns 0 as a result of each batch statement if total count was 0, and the Statement.SUCCESS_NO_INFO as a result of each batch statement if total count was > 0.

在 MySQL 中，rewriteBatchedStatements 的默认值取决于 MySQL 版本和 JDBC 驱动程序的配置。

• 在 MySQL 8.0 版本之前，rewriteBatchedStatements 的默认值是 false，这意味着批处理语句不会被重写优化。
• 从 MySQL 8.0 版本开始，rewriteBatchedStatements 的默认值被更改为 true，以提高默认情况下的性能。

要减少 JDBC 的网络调用次数改善性能，你可以设置 rewriteBatchedStatements=true，并使用 PreparedStatement 的 addBatch() 方法并执行 executeBatch() 批量发送多个操作给数据库。

根据执行的 DML 语句类型，使用不同的处理方法：

• 如果是 INSERT 语句，会整合成形如：insert into t values (xx),(yy),(zz),...
• 如果是 UPDATE/DELETE 语句，会整合成形如：update t set … where id = 1; update t set … where id = 2; update t set … where id = 3; ...

然后按 maxAllowedPacket 分批拼接 SQL 语句，然后按批次提交 MySQL。

参考：《MySQL 批量操作》

maxAllowedPacket

Maximum allowed packet size to send to server. If not set, the value of system variable max_allowed_packet will be used to initialize this upon connecting. This value will not take effect if set larger than the value of max_allowed_packet. Also, due to an internal dependency with the property “blobSendChunkSize“, this setting has a minimum value of “8203” if “useServerPrepStmts“ is set to “true“.

参考：《MySQL 批量插入数据，一次插入多少行数据效率最高？》

注册驱动程序

有几种方式可以注册驱动程序，如下：

手工注册

1
2
3

Class.forName("com.mysql.jdbc.Driver");  // 方式一，底层实现其实就是方式二
DriverManager.registerDriver(new com.mysql.jdbc.Driver());  // 方式二
System.setProperty("jdbc.drivers", "com.mysql.jdbc.Driver");  // 方式三

自动注册

从 JDBC API 4.0 开始，java.sql.DriverManager 类得到了增强，利用 Java SPI 机制从厂商驱动程序的 META-INF/services/java.sql.Driver 文件中自动加载 java.sql.Driver 实现类。 因此应用程序无需再显式调用 Class.forName 或 DriverManager.registerDriver 方法来注册或加载驱动程序。java.sql.DriverManager 源码分析如下，

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75

public class DriverManager {
    
    /**
     * Load the initial JDBC drivers by checking the System property
     * jdbc.properties and then use the {@code ServiceLoader} mechanism
     */
    static {
        loadInitialDrivers();
        println("JDBC DriverManager initialized");
    }

    private static void loadInitialDrivers() {
        String drivers;
        try {
            drivers = AccessController.doPrivileged(new PrivilegedAction<String>() {
                public String run() {
                    return System.getProperty("jdbc.drivers");
                }
            });
        } catch (Exception ex) {
            drivers = null;
        }
        // If the driver is packaged as a Service Provider, load it.
        // Get all the drivers through the classloader
        // exposed as a java.sql.Driver.class service.
        // ServiceLoader.load() replaces the sun.misc.Providers()

        AccessController.doPrivileged(new PrivilegedAction<Void>() {
            public Void run() {

                ServiceLoader<Driver> loadedDrivers = ServiceLoader.load(Driver.class);
                Iterator<Driver> driversIterator = loadedDrivers.iterator();

                /* Load these drivers, so that they can be instantiated.
                 * It may be the case that the driver class may not be there
                 * i.e. there may be a packaged driver with the service class
                 * as implementation of java.sql.Driver but the actual class
                 * may be missing. In that case a java.util.ServiceConfigurationError
                 * will be thrown at runtime by the VM trying to locate
                 * and load the service.
                 *
                 * Adding a try catch block to catch those runtime errors
                 * if driver not available in classpath but it's
                 * packaged as service and that service is there in classpath.
                 */
                try{
                    while(driversIterator.hasNext()) {
                        driversIterator.next();
                    }
                } catch(Throwable t) {
                // Do nothing
                }
                return null;
            }
        });

        println("DriverManager.initialize: jdbc.drivers = " + drivers);

        if (drivers == null || drivers.equals("")) {
            return;
        }
        String[] driversList = drivers.split(":");
        println("number of Drivers:" + driversList.length);
        for (String aDriver : driversList) {
            try {
                println("DriverManager.Initialize: loading " + aDriver);
                Class.forName(aDriver, true,
                        ClassLoader.getSystemClassLoader());
            } catch (Exception ex) {
                println("DriverManager.Initialize: load failed: " + ex);
            }
        }
    }

}

从上面源码中可见，当类加载器载入 java.sql.DriverManager 类时，会执行其静态代码块，从而执行 loadInitialDrivers() 方法。该方法实现中通过 Java SPI ServiceLoader 查找 classpath 下所有 jar 包内的 META-INF/services 目录，找到 java.sql.Driver 文件，加载其中定义的实现类并通过反射创建实例。以 mysql-connector-java 8.x 为例，该类定义就是 com.mysql.cj.jdbc.Driver，此时由于该 Driver 类内含静态代码块，会用 new 关键字创建自身实例并反向注册到 DriverManager，从而达到自动注册驱动程序的效果：

1
2
3
4
5
6
7
8
9
10
11
12

public class Driver extends NonRegisteringDriver implements java.sql.Driver {

    // Register ourselves with the DriverManager
    static {
        try {
            java.sql.DriverManager.registerDriver(new Driver());
        } catch (SQLException E) {
            throw new RuntimeException("Can't register driver!");
        }
    }

}

常见问题

如果有多个不同的驱动程序都被注册，调用 DriverManager.getConnection 方法通过 JDBC URL 获取数据源连接时，会使用第一个可用的驱动程序来创建连接。源码分析如下：

1

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useUnicode=true;characterEncoding=utf-8")

DriverManager 会遍历已注册的驱动程序，尝试获取连接，关键代码：Connection con = aDriver.driver.connect(url, info);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

// DriverManager 源码

private static Connection getConnection(
        String url, java.util.Properties info, Class<?> caller) throws SQLException {

    ...
    
    for(DriverInfo aDriver : registeredDrivers) {
        // If the caller does not have permission to load the driver then
        // skip it.
        if(isDriverAllowed(aDriver.driver, callerCL)) {
            try {
                println("    trying " + aDriver.driver.getClass().getName());
                Connection con = aDriver.driver.connect(url, info);
                if (con != null) {
                    // Success!
                    println("getConnection returning " + aDriver.driver.getClass().getName());
                    return (con);
                }
            } catch (SQLException ex) {
                if (reason == null) {
                    reason = ex;
                }
            }

        } else {
            println("    skipping: " + aDriver.getClass().getName());
        }

    }
    
    ...
    
}

MySQL 驱动程序实现类会判断该 JDBC URL 是否支持：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

// com.mysql.cj.jdbc.NonRegisteringDriver 源码，实现 java.sql.Driver 接口

@Override
public java.sql.Connection connect(String url, Properties info) throws SQLException {

    try {
        if (!ConnectionUrl.acceptsUrl(url)) {
            /*
             * According to JDBC spec:
             * The driver should return "null" if it realizes it is the wrong kind of driver to connect to the given URL. This will be common, as when the
             * JDBC driver manager is asked to connect to a given URL it passes the URL to each loaded driver in turn.
             */
            return null;
        }

        ConnectionUrl conStr = ConnectionUrl.getConnectionUrlInstance(url, info);
        switch (conStr.getType()) {
            case SINGLE_CONNECTION:
                return com.mysql.cj.jdbc.ConnectionImpl.getInstance(conStr.getMainHost());

            case LOADBALANCE_CONNECTION:
                return LoadBalancedConnectionProxy.createProxyInstance((LoadbalanceConnectionUrl) conStr);

            case FAILOVER_CONNECTION:
                return FailoverConnectionProxy.createProxyInstance(conStr);

            case REPLICATION_CONNECTION:
                return ReplicationConnectionProxy.createProxyInstance((ReplicationConnectionUrl) conStr);

            default:
                return null;
        }

    } catch (UnsupportedConnectionStringException e) {
        // when Connector/J can't handle this connection string the Driver must return null
        return null;

    } catch (CJException ex) {
        throw ExceptionFactory.createException(UnableToConnectException.class,
                  Messages.getString("NonRegisteringDriver.17", new Object[] { ex.toString() }), ex);
    }
}

scheme 支持列表：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

// com.mysql.cj.conf.Type 枚举源码

/**
 * The database URL type which is determined by the scheme section of the connection string.
 */
public enum Type {
    SINGLE_CONNECTION("jdbc:mysql:", HostsCardinality.SINGLE), //
    FAILOVER_CONNECTION("jdbc:mysql:", HostsCardinality.MULTIPLE), //
    LOADBALANCE_CONNECTION("jdbc:mysql:loadbalance:", HostsCardinality.ONE_OR_MORE), //
    REPLICATION_CONNECTION("jdbc:mysql:replication:", HostsCardinality.ONE_OR_MORE), //
    XDEVAPI_SESSION("mysqlx:", HostsCardinality.ONE_OR_MORE);

    private String scheme;
    private HostsCardinality cardinality;
    
    ...
}

如果该 JDBC URL 没有对应可用的驱动程序，程序将抛出异常：java.sql.SQLException: No suitable driver found for jdbc:...。

参考

https://en.wikipedia.org/wiki/JDBC_driver

https://www.javatpoint.com/jdbc-driver

https://blog.csdn.net/autfish/article/details/52170053

https://dev.mysql.com/downloads/connector/j/

https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-overview.html

《MySQL 驱动 Bug 引发的事务不回滚问题》

基础语法

SELECT 基础语法如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

SELECT
    [ALL | DISTINCT]
    select_expr [, select_expr ...]
    [
      FROM table_references
      [WHERE where_condition]
      [GROUP BY {col_name | expr | position}
        [ASC | DESC], ... [WITH ROLLUP]]
      [HAVING where_condition]
      [ORDER BY {col_name | expr | position}
        [ASC | DESC], ...]
      [LIMIT {[offset,] row_count | row_count OFFSET offset}]
      [
        INTO OUTFILE 'file_name'
          [CHARACTER SET charset_name]
          export_options
        | INTO DUMPFILE 'file_name'
        | INTO var_name [, var_name]
      ]
      [FOR UPDATE | LOCK IN SHARE MODE]
    ]

SELECT 语句可用于检索单个、多个、所有列（星号 * 通配符）。每个 select_expr 表示您想要检索的列。必须至少有一个 select_expr。

去重

修饰符 ALL 和 DISTINCT 用于指定重复行是否应该返回（是否去重），作用于所有的列，而不仅仅是跟在其后的那一列。例如 SELECT DISTINCT vend_id, prod_price ，除非指定的两列完全相同，否则所有的行都会被检索出来。

检索表

table_references 指示检索表，其语法可参考 JOIN语法。SELECT 也可以不使用 FROM 子句而用来检索计算出的行：

1
2

SELECT 1 + 1;
 -> 2

也可以使用 FROM DUAL 指定虚拟表，MySQL 会忽略这个子句。

1
2

SELECT 1 + 1 FROM DUAL;
 -> 2

过滤数据

WHERE 子句用于过滤数据，where_condition 可以使用以下表达式语法：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

expr:
    expr OR expr
  | expr || expr
  | expr XOR expr
  | expr AND expr
  | expr && expr
  | NOT expr
  | ! expr
  | boolean_primary IS [NOT] {TRUE | FALSE | UNKNOWN}
  | boolean_primary

boolean_primary:
    boolean_primary IS [NOT] NULL
  | boolean_primary <=> predicate
  | boolean_primary comparison_operator predicate
  | boolean_primary comparison_operator {ALL | ANY} (subquery)
  | predicate

comparison_operator: = | >= | > | <= | < | <> | !=

predicate:
    bit_expr [NOT] IN (subquery)
  | bit_expr [NOT] IN (expr [, expr] ...)
  | bit_expr [NOT] BETWEEN bit_expr AND predicate
  | bit_expr SOUNDS LIKE bit_expr
  | bit_expr [NOT] LIKE simple_expr [ESCAPE simple_expr]
  | bit_expr [NOT] REGEXP bit_expr
  | bit_expr

bit_expr:
  ...

simple_expr:
  ...

LIKE 可使用以下通配符：

排序数据

ORDER BY 子句用于排序，使用以下关键字进行升序或降序排序，要注意关键字只应用于直接位于其前面的列名。如果想在多个列上进行降序排序，必须对每一列指定 DESC 关键字。

限制结果集

LIMIT 子句可用于限制 SELECT 语句返回的结果集：

1
2
3

SELECT * FROM tbl LIMIT 5;     # Retrieve first 5 rows, equivalent to LIMIT 0, 5
SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15
SELECT * FROM tbl LIMIT 5 OFFSET 10;  # Retrieve rows 11-15

汇总数据

我们经常需要汇总数据而不用把它们实际检索出来，为此 SQL 提供了五个聚集函数（aggregate function）。使用这些函数，SQL 查询可用于检索数据，以便分析和报表生成。这种类型的检索例子有：

• 确定表中行数（或者满足某个条件或包含某个特定值的行数）；
• 获得表中某些行的和；
• 找出表列（或所有行或某些特定的行）的最大值、最小值、平均值。

修饰符 ALL 和 DISTINCT 可用于指定重复行是否应该返回。例如：

1
2
3
4
5
6
7
8
9
10
11

# 返回特定供应商所提供产品的平均价格
SELECT AVG(prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';
 -> 3.8650

# 同上，但平均值只考虑各个不同的价格
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';
 -> 4.2400

可以看到，使用了 DISTINCT 后的 avg_price 会比较高，因为此例子中有多个物品具有相同的较低价格，排除它们提升了平均价格。

SELECT 语句也可根据需要同时使用多个聚集函数：

1
2
3
4
5

SELECT COUNT(*) AS num_items,
       MIN(prod_price) AS price_min,
       MAX(prod_price) AS price_max,
       AVG(prod_price) AS price_avg
FROM Products;

分组数据

GROUP BY 子句用于分组数据，注意：

• GROUP BY 子句可以包含任意数目的列，因而可以对分组进行嵌套，更细致地进行数据分组。
• 如果在 GROUP BY 子句中嵌套了分组，数据将在最后指定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。
• GROUP BY 子句中列出的每一列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在 SELECT 中使用表达式，则必须在 GROUP BY 子句中指定相同的表达式。不能使用别名。
• 除聚集计算语句外，SELECT 语句中的每一列都必须在 GROUP BY 子句中给出。否则，如果 SELECT 语句中出现了 GROUP BY 中没有的列，假如该分组内的条目数大于 1，这样的列显示的内容为第一个条目的值。
• 如果分组列中包含具有 NULL 值的行，则 NULL 将作为一个分组返回。如果列中有多行 NULL 值，它们将分为一组。

GROUP BY 可以搭配使用 HAVING 过滤分组。HAVING 和 WHERE 的差别在于，WHERE 对分组前的数据进行过滤， HAVING 对分组后的数据进行过滤。

加锁读

InnoDB 支持两种类型的 加锁读（Locking Reads），为事务操作提供额外的安全性：

• SELECT ... LOCK IN SHARE MODE 设置共享（S）锁
• SELECT ... FOR UPDATE 设置排它（X）锁

详情请参考《MySQL 锁机制总结》。

UNION 子句

语法：

1
2
3

SELECT ...
UNION [ALL | DISTINCT] SELECT ...
[UNION [ALL | DISTINCT] SELECT ...]

UNION 将来自多个 SELECT 语句的结果合并为一个结果集，结果集列名取自第一条 SELECT 语句的列名。

UNION 或 UNION DISTINCT 去重，而 UNION ALL 则不去重。

要为单独的一条 SELECT 语句应用 ORDER BY 或 LIMIT 子句，需要将子句放在包含 SELECT 的括号内，例如：

1
2
3

(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

反之，如要应用到整个 UNION 结果集，则在单个 SELECT 语句后面加上括号，并在最后一个语句后面加上子句，例如：

1
2
3
4

(SELECT a FROM t1 WHERE a=10 AND B=1)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2)
ORDER BY a LIMIT 10;

参考

《MySQL 必知必会》

https://dev.mysql.com/doc/refman/5.7/en/select.html

https://dev.mysql.com/doc/refman/5.7/en/innodb-locking-reads.html

日期函数

获得当前日期/时间

注意，MySQL 时间支持的最高存储精度为微秒：

1 秒(s) =
1,000 毫秒(ms) =
1,000,000 微秒(μs) =
1,000,000,000 纳秒(ns) =
1,000,000,000,000 皮秒(ps) =
1,000,000,000,000,000 飞秒(fs) =
1,000,000,000,000,000,000 仄秒(zs) =
1,000,000,000,000,000,000,000 幺秒(ys) =
1,000,000,000,000,000,000,000,000 渺秒(as)

1 微秒(μs) = 10^-6 秒（0.000,001，百万分之一秒）
1 毫秒(ms) = 10^-3 秒（0.001，千分之一秒）

因此 fsp 参数范围只能为 0 ~ 6：

If the fsp argument is given to specify a fractional seconds precision from 0 to 6, the return value includes a fractional seconds part of that many digits.

例子：

1
2
3
4
5
6

SELECT CURDATE();                       -- 2018-08-08，获取当前年月日
SELECT CURTIME();                       -- 22:41:30，获取当前时分秒
SELECT NOW();                           -- 2018-08-08 22:20:46，获取当前年月日时分秒
SELECT NOW(3);                          -- 2018-08-08 22:20:46.166，获取当前年月日时分秒毫秒
SELECT NOW(6);                          -- 2018-08-08 22:20:46.166123，获取当前年月日时分秒毫秒微秒
SELECT CURRENT_TIMESTAMP;               -- 2018-08-08 22:20:46，获取当前年月日时分秒

时区查看/修改

5.1.13 MySQL Server Time Zone Support

查看当前时区

1
2
3
4
5
6
7

-- 结果主要看 system_time_zone
show variables like '%time_zone%';

-- 查询系统时区、会话时区、下一事务时区
SELECT @@GLOBAL.time_zone, 
       @@SESSION.time_zone, 
       @@time_zone;;

参考：MySQL 中几个关于时间/时区的变量

修改时区

通过 SQL SET 语法临时修改：

1
2
3
4
5
6
7
8

-- 设置 Global 全局时区，重启后失效
set global time_zone = '+8:00';

-- 设置 Session 会话时区，会话关闭后失效
set time_zone = '+8:00';

-- 设置 下一事务 时区，事务结束后失效
set @@time_zone = '+8:00';

通过修改配置文件，重启后永久生效：

1
2
3
4

$ vim /etc/mysql/my.cnf
default-time_zone = '+8:00'

$ service mysql restart

时区转换 函数

CONVERT_TZ(dt, from_tz, to_tz) 函数用于将 DATETIME 类型转为指定时区，例如：

1
2
3
4
5
6
7
8
9
10
11

-- TIMESTAMP 类型
-- 1218124800，获取当前时间戳
SELECT UNIX_TIMESTAMP();

-- DATETIME 类型
-- 2008-08-07 16:00:00 UTC±00:00
SELECT FROM_UNIXTIME( UNIX_TIMESTAMP() );

-- TIMESTAMP 类型 > DATETIME 类型 > DATETIME 类型
-- 2008-08-08 00:00:00 UTC+08:00
SELECT CONVERT_TZ( FROM_UNIXTIME( UNIX_TIMESTAMP() ), '+00:00', '+08:00' ) AS NOW;

1
2
3

-- DATETIME 类型
-- 2008-08-08 00:00:00 UTC+08:00
SELECT CONVERT_TZ( '2008-08-07 16:00:00', '+00:00', '+08:00' );

TIMESTAMP 类型的时区显示，参考：https://dev.mysql.com/doc/refman/5.7/en/datetime.html

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server’s time. The time zone can be set on a per-connection basis. As long as the time zone setting remains constant, you get back the same value you store. If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions. The current time zone is available as the value of the time_zone system variable. For more information, see Section 5.1.13, “MySQL Server Time Zone Support”.

日期/时间类型转换 函数

TIMESTAMP → xxx

FROM_UNIXTIME(unix_timestamp[,format])

Returns a representation of unix_timestamp as a DATETIME or VARCHAR value. The value returned is expressed using the session time zone. (Clients can set the session time zone as described in Section 5.1.13, “MySQL Server Time Zone Support”.) unix_timestamp is an internal timestamp value representing seconds since '1970-01-01 00:00:00' UTC, such as produced by the UNIX_TIMESTAMP() function.

unix_timestamp

• When unix_timestamp is an integer, the fractional seconds precision of the DATETIME is 0.
• When unix_timestamp is a decimal value, the fractional seconds precision of the DATETIME is the same as the precision of the decimal value, up to a maximum of 6.
• When unix_timestamp is a floating point number, the fractional seconds precision of the datetime is 6.

format

• If format is omitted, the value returned is a DATETIME.
• If format is supplied, the value returned is a VARCHAR.

例子：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

-- 不指定 format 格式，返回 DATETIME 类型
SELECT FROM_UNIXTIME(1447430881);
 -> '2015-11-13 10:08:01'

-- 只支持单位为秒的时间戳，不支持毫秒、微秒，需要先除以其精度转为浮点数
SELECT FROM_UNIXTIME(1447430881123 / 1000);
 -> '2015-11-13 16:08:01.1230'

-- 运算后，转为整数类型
SELECT FROM_UNIXTIME(1447430881) + 0;
 -> 20151113100801

-- 指定 format 格式，返回 VARCHAR 类型
SELECT FROM_UNIXTIME(1447430881, '%Y %D %M %h:%i:%s %x');
 -> '2015 13th November 10:08:01 2015'

fomart 参数参考这里。

xxx → TIMESTAMP

UNIX_TIMESTAMP([date])

Return a Unix timestamp.

If no date argument, it returns a Unix timestamp representing seconds since '1970-01-01 00:00:00' UTC.

If with a date argument, it returns the value of the argument as seconds since '1970-01-01 00:00:00' UTC.

The server interprets date as a value in the session time zone and converts it to an internal Unix timestamp value in UTC. (Clients can set the session time zone as described in Section 5.1.13, “MySQL Server Time Zone Support”.)

The date argument may be

• a DATE, DATETIME, or TIMESTAMP string,
• or a number in YYMMDD, YYMMDDhhmmss, YYYYMMDD, or YYYYMMDDhhmmss format.

If the argument includes a time part, it may optionally include a fractional seconds part.

The return value is

• an integer if no argument is given or the argument does not include a fractional seconds part,
• or DECIMAL if an argument is given that includes a fractional seconds part.

When the date argument is a TIMESTAMP column, UNIX_TIMESTAMP() returns the internal timestamp value directly, with no implicit “string-to-Unix-timestamp” conversion.

The valid range of argument values is the same as for the TIMESTAMP data type: '1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.999999' UTC. If you pass an out-of-range date to UNIX_TIMESTAMP(), it returns 0.

例子：

1
2
3
4
5
6
7
8

-- 1218124800，获取当前时间戳
SELECT UNIX_TIMESTAMP();
-- 1218124800，将当前时间转换为时间戳，等价于上例
SELECT UNIX_TIMESTAMP(now());
-- 1218153600，即：2008-08-08 00:00:00 UTC
SELECT UNIX_TIMESTAMP('2008-08-08 00:00:00');
-- 1218128400，即：2008-08-07 17:00:00 UTC
SELECT UNIX_TIMESTAMP( CONVERT_TZ( '2008-08-08 00:00:00', '+07:00', '+00:00' ) );

DATETIME → String

Date/Time to Str（日期/时间转换为字符串）函数：

DATE_FORMAT(date, format)、TIME_FORMAT(time, format)

例子：

1
2
3
4

select date_format(now(), '%Y-%m-%d');                          -- 2018-08-08
select date_format('2018-08-08 22:23:00', '%W %M %Y');          -- Friday August 2018
select date_format('2018-08-08 22:23:01', '%Y%m%d%H%i%s');      -- 20180808222301
select time_format('22:23:01', '%H.%i.%s');                     -- 22.23.01

String → DATETIME

STR_TO_DATE(str, format)

This is the inverse of the DATE_FORMAT() function. It takes a string str and a format string format. STR_TO_DATE() returns a DATETIME value if the format string contains both date and time parts, or a DATE or TIME value if the string contains only date or time parts. If the date, time, or datetime value extracted from str is illegal, STR_TO_DATE() returns NULL and produces a warning.

例子：

1
2
3
4
5

select str_to_date('08/09/2008', '%m/%d/%Y');                   -- 2008-08-09
select str_to_date('08/09/08'  , '%m/%d/%y');                   -- 2008-08-09
select str_to_date('08.09.2008', '%m.%d.%Y');                   -- 2008-08-09
select str_to_date('08:09:30', '%h:%i:%s');                     -- 08:09:30
select str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s'); -- 2008-08-09 08:09:30

format 参数

format 参数如下，这里只列出常用的。更多 format 参数参考：

https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format

日期/时间计算 函数

为日期增加一个时间间隔：date_add()

为日期减去一个时间间隔：date_sub()

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

set @dt = now();

select date_add(@dt, interval 1 day);        -- add 1 day
select date_add(@dt, interval 1 hour);       -- add 1 hour
select date_add(@dt, interval 1 minute);     -- ...
select date_add(@dt, interval 1 second);
select date_add(@dt, interval 1 microsecond);
select date_add(@dt, interval 1 week);
select date_add(@dt, interval 1 month);
select date_add(@dt, interval 1 quarter);
select date_add(@dt, interval 1 year);

select date_add(@dt, interval -1 day);       -- sub 1 day

SELECT DATE_SUB(@dt, INTERVAL 7 DAY);        -- 七天前

日期/时间截取 函数

选取日期时间的各个部分：日期、时间、年、季度、月、日、小时、分钟、秒、微秒

1
2
3
4
5
6
7
8
9
10
11
12
13

set @dt = now();

select date(@dt);        -- 2008-09-10
select time(@dt);        -- 07:15:30.123456
select year(@dt);        -- 2008
select quarter(@dt);     -- 3
select month(@dt);       -- 9
select week(@dt);        -- 36
select day(@dt);         -- 10
select hour(@dt);        -- 7
select minute(@dt);      -- 15
select second(@dt);      -- 30
select microsecond(@dt); -- 123456

例子

按年/月/日/时统计订单量

按年统计：DATE_FORMAT(create_time,'%Y')

按月统计：DATE_FORMAT(create_time,'%Y-%m')

按日统计：DATE_FORMAT(create_time,'%Y-%m-%d')

按时统计：DATE_FORMAT(create_time,'%Y-%m-%d %H:00:00')

按日统计订单量，如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

select count(*), DATE_FORMAT(create_time,'%Y-%m-%d') AS days 
from t_order 
where create_time BETWEEN '2008-9-29' AND '2008-9-30' 
group by days;

+----------+------------+
| count(*) | hours      |
+----------+------------+
|   150628 | 2008-09-01 |
|   172419 | 2008-09-02 |
|   177021 | 2008-09-03 |
|   178917 | 2008-09-04 |
|   180960 | 2008-09-05 |
|   177626 | 2008-09-06 |
|   177504 | 2008-09-07 |
|   166118 | 2008-09-08 |
|   193006 | 2008-09-09 |
|   204156 | 2008-09-10 |
|   196598 | 2008-09-11 |
|   200184 | 2008-09-12 |
|   159169 | 2008-09-13 |
|   179798 | 2008-09-14 |
|   203586 | 2008-09-15 |
|   217863 | 2008-09-16 |
|   231207 | 2008-09-17 |
|   245960 | 2008-09-18 |
|   226578 | 2008-09-19 |
|   211986 | 2008-09-20 |
|   201396 | 2008-09-21 |
|   183012 | 2008-09-22 |
|   221780 | 2008-09-23 |
|   228094 | 2008-09-24 |
|   220251 | 2008-09-25 |
|   240866 | 2008-09-26 |
|   235670 | 2008-09-27 |
|   244964 | 2008-09-28 |
|    98805 | 2008-09-29 |
+----------+------------+
29 rows in set (5.83 sec)

按 N 分钟统计订单量

做法在于将每行的分钟数 MINUTE(create_time) 除以 10 得到的小数使用 FLOOR 函数向下取整，再乘以 10 得到的就是所属区间。例如：

• 1 分钟 -> 0
• 25 分钟 -> 20
• 59 分钟 -> 50

下例按 10 分钟统计订单量：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

SELECT COUNT(*), DATE_FORMAT(
  CONCAT(
    DATE(create_time), 
    ' ', 
    HOUR(create_time), 
    ':', 
    FLOOR(MINUTE(create_time) / 10) * 10
  ), '%Y-%m-%d %H:%i'
) AS hours
FROM t_order
WHERE create_time BETWEEN '2008-9-29' AND '2008-9-30'
GROUP BY hours;

+----------+------------------+
| COUNT(*) | hours            |
+----------+------------------+
|       51 | 2008-09-29 00:00 |
|       53 | 2008-09-29 00:10 |
|       43 | 2008-09-29 00:20 |
|       59 | 2008-09-29 00:30 |
|       40 | 2008-09-29 00:40 |
|       27 | 2008-09-29 00:50 |
|       22 | 2008-09-29 01:00 |
|       24 | 2008-09-29 01:10 |
|       15 | 2008-09-29 01:20 |
|       15 | 2008-09-29 01:30 |
|       26 | 2008-09-29 01:40 |
|       18 | 2008-09-29 01:50 |
|     1949 | 2008-09-29 02:00 |

参考

https://dev.mysql.com/doc/refman/5.7/en/functions.html

https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html

https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html

https://dev.mysql.com/doc/refman/5.7/en/time-zone-support.html

原则

MySQL 支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重要。不管存储哪种类型的数据，下面几个简单的原则都有助于作出更好的选择。

Smaller is usually better

更小的通常更好：

In general, try to use the smallest data type that can correctly store and represent your data. Smaller data types are usually faster, because they use less space on the disk, in memory, and in the CPU cache. They also generally require fewer CPU cycles to process.

Make sure you don’t underestimate the range of values you need to store, though, because increasing the data type range in multiple places in your schema can be a painful and time-consuming operation. If you’re in doubt as to which is the best data type to use, choose the smallest one that you don’t think you’ll exceed. (If the system is not very busy or doesn’t store much data, or if you’re at an early phase in the design process, you can change it easily later.)

更小的数据类型通常更快，因为占用更少的磁盘、内存和 CPU 缓存，并且处理时需要的 CPU 周期也更少。

Simple is good

简单就好：

Fewer CPU cycles are typically required to process operations on simpler data types. For example, integers are cheaper to compare than characters, because character sets and collations (sorting rules) make character comparisons complicated. Here are two examples: you should store dates and times in MySQL’s builtin types instead of as strings, and you should use integers for IP addresses. We discuss these topics further later.

简单数据类型的操作通常需要更少的 CPU 周期、索引性能更好。例如，整型比字符操作代价更低，因为字符集和校对规则（排序规则）使得字符比整型更复杂。

有几个例子：

• 使用日期与时间类型，而不是字符串来存储日期和时间，以便排序和格式转换。

• IPv4 地址：按十进制标记法（32-bit decimal notation）使用 32 位无符号整型类型（int unsigned）进行存储，而不是按点分十进制标记法（Dot-decimal notation）使用字符串类型（varchar）进行存储。

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

  -- 测试表（注意使用 int UNSIGNED 类型存储（存储范围：0 ~ 2^32-1）） CREATE TABLE `t_iptable` ( `ip1` int(32) UNSIGNED NOT NULL COMMENT 'IPv4 地址', `ip2` varchar(15) NOT NULL COMMENT 'IPv4 地址' ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- 测试数据 INSERT INTO t_iptable(ip1, ip2) VALUES(INET_ATON('0.0.0.0'), '0.0.0.0'); INSERT INTO t_iptable(ip1, ip2) VALUES(INET_ATON('10.198.1.1'), '10.198.1.1'); INSERT INTO t_iptable(ip1, ip2) VALUES(INET_ATON('255.255.255.255'), '255.255.255.255'); -- 测试结果 -- INET_ATON() Return the numeric value of an IP address -- INET_NTOA() Return the IP address from a numeric value -- INET6_ATON() Return the numeric value of an IPv6 address -- INET6_NTOA() Return the IPv6 address from a numeric value SELECT INET_NTOA(ip1), ip2 FROM t_iptable; +-----------------+-----------------+ | INET_NTOA(ip1) | ip2 | +-----------------+-----------------+ | 0.0.0.0 | 0.0.0.0 | | 10.198.1.1 | 10.198.1.1 | | 255.255.255.255 | 255.255.255.255 | +-----------------+-----------------+

• 散列值：使用定长二进制类型（binary），而不是按十六进制标记法使用字符串类型（char）来存储散列值：https://stackoverflow.com/questions/614476/storing-sha1-hash-values-in-mysql

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

  -- 测试表 CREATE TABLE `t_hash` ( `hash1` binary(20) NOT NULL COMMENT '散列值', `hash2` char(40) NOT NULL COMMENT '散列值' ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- 测试数据 INSERT INTO t_hash(hash1, hash2) VALUES(0xE562F69EC36E625116376F376D991E41613E9BF3, 'E562F69EC36E625116376F376D991E41613E9BF3'); INSERT INTO t_hash(hash1, hash2) VALUES(X'E562F69EC36E625116376F376D991E41613E9BF3', 'E562F69EC36E625116376F376D991E41613E9BF3'); INSERT INTO t_hash(hash1, hash2) VALUES(UNHEX('E562F69EC36E625116376F376D991E41613E9BF3'), 'E562F69EC36E625116376F376D991E41613E9BF3'); -- 测试结果 -- BIT_LENGTH() Return length of a string in bits -- LENGTH() Return length of a string in bytes SELECT HEX(hash1), BIT_LENGTH(hash1), LENGTH(hash1), hash2, BIT_LENGTH(hash2), LENGTH(hash2) FROM t_hash; +------------------------------------------+-------------------+---------------+------------------------------------------+-------------------+---------------+ | HEX(hash1) | BIT_LENGTH(hash1) | LENGTH(hash1) | hash2 | BIT_LENGTH(hash2) | LENGTH(hash2) | +------------------------------------------+-------------------+---------------+------------------------------------------+-------------------+---------------+ | E562F69EC36E625116376F376D991E41613E9BF3 | 160 | 20 | E562F69EC36E625116376F376D991E41613E9BF3 | 320 | 40 | +------------------------------------------+-------------------+---------------+------------------------------------------+-------------------+---------------+

Avoid NULL if possible

避免使用 NULL：

A lot of tables include nullable columns even when the application does not need to store NULL (the absence of a value), merely because it’s the default. It’s usually best to specify columns as NOT NULL unless you intend to store NULL in them.

It’s harder for MySQL to optimize queries that refer to nullable columns, because they make indexes, index statistics, and value comparisons more complicated. A nullable column uses more storage space and requires special processing inside MySQL. When a nullable column is indexed, it requires an extra byte per entry and can even cause a fixed-size index (such as an index on a single integer column) to be converted to a variable-sized one in MyISAM.

The performance improvement from changing NULL columns to NOT NULL is usually small, so don’t make it a priority to find and change them on an existing schema unless you know they are causing problems. However, if you’re planning to index columns, avoid making them nullable if possible.

There are exceptions, of course. For example, it’s worth mentioning that InnoDB stores NULL with a single bit, so it can be pretty space-efficient for sparsely populated data. This doesn’t apply to MyISAM, though.

NULL 列使得 MySQL 索引、索引统计和值比较都更复杂。值可为 NULL 的列会使用更多的存储空间（例如当可为 NULL 的列被索引时，每个索引记录需要一个额外的字节），在 MySQL 里也需要特殊处理。如果计划在列上建索引，应该尽量避免。

你应该用 0、一个特殊的值或者一个空串代替 NULL值。