MySQL JDBC 驱动程序无法创建带有连字符的模式

Question

我有一个具有每个租户架构架构的应用程序。但是当租户名称包含连字符时，模式创建失败。数据源创建:

HikariDataSource ds = new HikariDataSource();
ds.setUsername(username);
ds.setPassword(password);
ds.setJdbcUrl("jdbc:mysql://localhost:3306/tenant-test");
ds.setDriverClassName("com.mysql.cj.jdbc.Driver");
ds.addDataSourceProperty( "allowMultiQueries" , "true" );
ds.addDataSourceProperty( "autoReconnect" , "true" );
ds.addDataSourceProperty( "characterEncoding" , "UTF-8" );
ds.addDataSourceProperty( "useUnicode" , "true" );
ds.addDataSourceProperty( "createDatabaseIfNotExist" , "true" );
ds.getConnection(); // exception thrown here

是否有任何我必须设置的属性？当模式存在时，一切正常。也成功创建了没有连字符的架构。根据堆栈跟踪，它看起来像是 mysql jdbc 驱动程序中的问题。

异常堆栈跟踪:

Caused by: java.sql.SQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:110) ~[mysql-connector-java-8.0.14.jar:8.0.14]
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.14.jar:8.0.14]
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89) ~[mysql-connector-java-8.0.14.jar:8.0.14]
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63) ~[mysql-connector-java-8.0.14.jar:8.0.14]
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73) ~[mysql-connector-java-8.0.14.jar:8.0.14]
    at com.mysql.cj.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:905) ~[mysql-connector-java-8.0.14.jar:8.0.14]
    at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:830) ~[mysql-connector-java-8.0.14.jar:8.0.14]
    at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:455) ~[mysql-connector-java-8.0.14.jar:8.0.14]
    at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:240) ~[mysql-connector-java-8.0.14.jar:8.0.14]
    at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:199) ~[mysql-connector-java-8.0.14.jar:8.0.14]
    at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:136) ~[HikariCP-3.2.0.jar:na]
    at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:369) ~[HikariCP-3.2.0.jar:na]
    at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:198) ~[HikariCP-3.2.0.jar:na]
    at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:467) ~[HikariCP-3.2.0.jar:na]
    at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:541) ~[HikariCP-3.2.0.jar:na]
    at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115) ~[HikariCP-3.2.0.jar:na]
    at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:112) ~[HikariCP-3.2.0.jar:na]
    at org.flywaydb.core.internal.jdbc.JdbcUtils.openConnection(JdbcUtils.java:56) ~[flyway-core-5.2.4.jar:na]
    ... 54 common frames omitted
Caused by: com.mysql.cj.exceptions.CJException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-test' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_144]
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_144]
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_144]
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_144]
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61) ~[mysql-connector-java-8.0.14.jar:8.0.14]
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105) ~[mysql-connector-java-8.0.14.jar:8.0.14]
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151) ~[mysql-connector-java-8.0.14.jar:8.0.14]
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:129) ~[mysql-connector-java-8.0.14.jar:8.0.14]
    at com.mysql.cj.protocol.a.NativeProtocol.checkErrorMessage(NativeProtocol.java:816) ~[mysql-connector-java-8.0.14.jar:8.0.14]
    at com.mysql.cj.protocol.a.NativeProtocol.checkErrorMessage(NativeProtocol.java:741) ~[mysql-connector-java-8.0.14.jar:8.0.14]
    at com.mysql.cj.protocol.a.NativeProtocol.sendCommand(NativeProtocol.java:671) ~[mysql-connector-java-8.0.14.jar:8.0.14]
    at com.mysql.cj.protocol.a.NativeProtocol.changeDatabase(NativeProtocol.java:543) ~[mysql-connector-java-8.0.14.jar:8.0.14]
    at com.mysql.cj.protocol.a.NativeAuthenticationProvider.proceedHandshakeWithPluggableAuthentication(NativeAuthenticationProvider.java:706) ~[mysql-connector-java-8.0.14.jar:8.0.14]
    at com.mysql.cj.protocol.a.NativeAuthenticationProvider.connect(NativeAuthenticationProvider.java:202) ~[mysql-connector-java-8.0.14.jar:8.0.14]
    at com.mysql.cj.protocol.a.NativeProtocol.connect(NativeProtocol.java:1449) ~[mysql-connector-java-8.0.14.jar:8.0.14]
    at com.mysql.cj.NativeSession.connect(NativeSession.java:165) ~[mysql-connector-java-8.0.14.jar:8.0.14]
    at com.mysql.cj.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:849) ~[mysql-connector-java-8.0.14.jar:8.0.14]

编辑:

我认为这是一个错误。我查看了 mysql 连接器源，发现数据库名称未被引用...

NativeProtocol.java，第 542 行:

if (this.getPropertySet().getBooleanProperty(PropertyKey.createDatabaseIfNotExist).getValue()) {
                sendCommand(this.commandBuilder.buildComQuery(getSharedSendPacket(), "CREATE DATABASE IF NOT EXISTS " + database), false, 0);
...

Answer 1

在 MySQL 中，破折号(连字符)字符在不带引号的标识符中不是允许的字符。

标识符的规则记录在 MySQL 引用手册中:

https://dev.mysql.com/doc/refman/8.0/en/identifiers.html

---

要使用连字符，标识符必须用引号引起来。通常我们使用反引号字符，例如

 SELECT `foo` FROM `bar` 

---

如果 sql_mode 包含 ANSI_QUOTES，我们可以使用双引号来转义标识符。

---

MySQL 报告无处不在的错误 1064。堆栈跟踪中报告了这一点:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-test' at line 1