场景:使用微信授权获取用户信息-昵称,头像地址等,保存用户昵称(带emoji表情,如☺)存储到数据库失败
Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\xAF' for column 'nickname' at row 1
如 之前的一篇 文章(当爬虫遇到emoji表情) 所说:需要修改数据库中库、表编码格式为 utf-8mb4
但目前我们就遭遇到了:数据库没权限!的情况,因为并不是每次都是在自己服务器上开发,所以只能通过SQL来设置:
尝试一下以下SQL
set names utf8mb4;
update user set nickname='☺' where id = 1
那么当使用Java(SpringBoot+Druid连接池+Mybatis)进行表情符号插入时也是
首先测试下使用以上SQL是否能够成功插入
1.配置application.properties中
spring.datasource.url=jdbc:mysql://localhost:3306/test?useSSL=false&allowMultiQueries=true
2.userMapper.xml中
<update id="updateUser">
set names utf8mb4;
update user set nickname='☺' where id = "1";
</update>
但是每次都使用 set names utf8mb4; 来设置连接肯定太过麻烦,所以需要采用一种方式对Java数据连接在初始化的时候就进行设置
在application.properties中添加连接池初始化时的设置
spring.datasource.connection-init-sqls=set names utf8mb4;
但是发现配置在这里的并没有执行,所以重写一个DruidConfig
import java.sql.SQLException;
import java.util.List;
import javax.sql.DataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import com.alibaba.druid.pool.DruidDataSource;
@Configuration
public class DruidConfig {
private Logger logger = LoggerFactory.getLogger(DruidConfig.class);
@Value("${spring.datasource.connection-init-sqls}")
private List<String> connectionInitSqls;
@Value("${spring.datasource.url}")
private String dbUrl;
@Value("${spring.datasource.username}")
private String username;
@Value("${spring.datasource.password}")
private String password;
@Value("${spring.datasource.driverClassName}")
private String driverClassName;
@Value("${spring.datasource.initialSize}")
private int initialSize;
@Value("${spring.datasource.minIdle}")
private int minIdle;
@Value("${spring.datasource.maxActive}")
private int maxActive;
@Value("${spring.datasource.maxWait}")
private int maxWait;
@Value("${spring.datasource.timeBetweenEvictionRunsMillis}")
private int timeBetweenEvictionRunsMillis;
@Value("${spring.datasource.minEvictableIdleTimeMillis}")
private int minEvictableIdleTimeMillis;
@Value("${spring.datasource.validationQuery}")
private String validationQuery;
@Value("${spring.datasource.testWhileIdle}")
private boolean testWhileIdle;
@Value("${spring.datasource.testOnBorrow}")
private boolean testOnBorrow;
@Value("${spring.datasource.testOnReturn}")
private boolean testOnReturn;
@Value("${spring.datasource.poolPreparedStatements}")
private boolean poolPreparedStatements;
@Value("${spring.datasource.maxPoolPreparedStatementPerConnectionSize}")
private int maxPoolPreparedStatementPerConnectionSize;
@Value("{spring.datasource.connectionProperties}")
private String connectionProperties;
@Bean //声明其为Bean实例
@Primary //在同样的DataSource中,首先使用被标注的DataSource
public DataSource dataSource(){
DruidDataSource datasource = new DruidDataSource();
datasource.setUrl(this.dbUrl);
datasource.setUsername(username);
datasource.setPassword(password);
datasource.setDriverClassName(driverClassName);
//configuration
datasource.setInitialSize(initialSize);
datasource.setMinIdle(minIdle);
datasource.setMaxActive(maxActive);
datasource.setMaxWait(maxWait);
datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
datasource.setValidationQuery(validationQuery);
datasource.setTestWhileIdle(testWhileIdle);
datasource.setTestOnBorrow(testOnBorrow);
datasource.setTestOnReturn(testOnReturn);
datasource.setPoolPreparedStatements(poolPreparedStatements);
datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
datasource.setConnectionInitSqls(connectionInitSqls);
// try {
datasource.setFilters(filters);
// } catch (SQLException e) {
// logger.error("druid configuration initialization filter", e);
// }
datasource.setConnectionProperties(connectionProperties);
return datasource;
}
}
这样在项目启动时就会设置了数据链接,从而解决问题,这样Java就不会报
Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\xAF' for column 'nickname' at row 1