分类 Mysql 下的文章

登录终端:

mysql -u root -h localhost -p

mysql operate

创建数据库

create database dbname default charset utf8mb4 COLLATE utf8mb4_general_ci;
utf8mb4可以存储更多特殊字符,应用场景包括微信昵称等

创建用户并授权

create user 'user1'@'%' identified by '123456';
grant all privileges on user1.* to 'dbname'@'%';
授权后能看到information_schema数据库,是正常的。里面包含了授权给他的信息,大部分是只读的,视图形式展示.

比如我只想让用户查询、插入、更新,不允许删除,那么可以换成以下SQL:
grant select,insert,update on test.* to 'test'@'%';

查询当前用户已经授权的权限:
show grants for current_user();

一、背景
在我们数据库中有些时候会保存一些用户的敏感信息,比如:手机号、银行卡等信息,如果这些信息以明文的方式保存,那么是不安全的。假如:黑客黑进了数据库,或者离职人员导出了数据,那么就可能导致这些敏感数据的泄漏。因此我们就需要找到一种方法来解决这个问题。

二、解决方案
由于我们系统中使用了Mybatis作为数据库持久层,因此决定使用Mybatis的TypeHandler或Plugin来解决。

TypeHandler : 需要我们在某些列上手动指定 typeHandler 来选择使用那个typeHandler或者根据@MappedJdbcTypes 和 @MappedTypes注解来自行推断。
<result column="phone" property="phone"
typeHandler="com.huan.study.mybatis.typehandler.EncryptTypeHandler"/>

Plugin : 可以拦截系统中的 select、insert、update、delete等语句,也能获取到sql执行前的参数和执行后的数据。
经过考虑,决定使用TypeHandler来加解密数据。

    /**
 * 加解密TypeHandler
 *
 * @author huan.fu 2021/5/18 - 上午9:20
 */
@MappedJdbcTypes(JdbcType.VARCHAR)
@MappedTypes(Encrypt.class)
public class EncryptTypeHandler extends BaseTypeHandler<Encrypt> {

    private static final byte[] KEYS = "12345678abcdefgh".getBytes(StandardCharsets.UTF_8);

    /**
     * 设置参数
     */
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, Encrypt parameter, JdbcType jdbcType) throws SQLException {
        if (parameter == null || parameter.getValue() == null) {
            ps.setString(i, null);
            return;
        }
        AES aes = SecureUtil.aes(KEYS);
        String encrypt = aes.encryptHex(parameter.getValue());
        ps.setString(i, encrypt);
    }

    /**
     * 获取值
     */
    @Override
    public Encrypt getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return decrypt(rs.getString(columnName));
    }

    /**
     * 获取值
     */
    @Override
    public Encrypt getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        return decrypt(rs.getString(columnIndex));
    }

    /**
     * 获取值
     */
    @Override
    public Encrypt getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        return decrypt(cs.getString(columnIndex));
    }

    public Encrypt decrypt(String value) {
        if (null == value) {
            return null;
        }
        return new Encrypt(SecureUtil.aes(KEYS).decryptStr(value));
    }
}

注意⚠️:

@MappedTypes:表示该处理器处理的java类型是什么。
@MappedJdbcTypes:表示处理器处理的Jdbc类型。

配置文件中指定Typehandler的包路径

mybatis.type-handlers-package=com.huan.study.mybatis.typehandler

36247-s83jc7mzx6o.png

从测试结果中可知,添加数据时,需要加密的数据(phone)在数据库中已经加密了,在查询的时候,加密的数据已经自动解密了。

实现代码参考

https://gitee.com/huan1993/spring-cloud-parent/tree/master/mybatis/mybatis-typehandler-encrypt

查询出来的数据库结束时间(字符串类型):2020-03-07 23:55:00.0
DateUtils.parse使用"yyyy-MM-dd HH:mm:ss"解析的时候报错。
java.text.ParseException: Unable to parse the date: 2020-03-07 23:55:00.0

解决方法:

//先转换成时间戳类型,再转化成date
Timestamp date_time= (Timestamp) MapUtils.getObject(map,"date_time");
Date fmtDate= new Date(date_time.getTime());
log.error("转换后日期:{}",DateUtils.formatDate(fmtDate,"yyyy-MM-dd HH:mm:ss"));

用得最多的日期时间类型,就是 DATETIME。虽然 MySQL 也支持 YEAR(年)、 TIME(时间)、DATE(日期),以及 TIMESTAMP 类型,但是在实际项目中,尽量用 DATETIME 类型。因为这个数据类型包括了完整的日期和时间信息,取值范围也最大,使用起来比较方便。毕竟,如果日期时间信息分散在好几个字段,很不容易记,而且查询的时候,SQL 语句也会更加复杂。

此外,一般存注册时间、商品发布时间等,不建议使用DATETIME存储,而是使用时间戳,因为DATETIME虽然直观,但不便于计算。

java.sql.SQLException: Unknown error 145
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2484)

CHECK TABLE tableName;
执行这个,如果表正常是下面的,如果不正常则会报表有错误,需要修复下
check table info

修复方式:

1.到数据目录下执行
myisamchk -r -c table_name.MYI

recovering (with keycache) MyISAM-table 'table_name.MYI'
Data records: 289
Data records: 288

2.Navicat 工具解决办法:
选中表->右键->Maintain->Repair Table->Quick,亲测有效。
解决问题。

使用 exists 代替 in 是一个好的选择:
select num from a where num in(select num from b)
用下面的语句替换:
select num from a where exists(select 1 from b where num=a.num)

exists语法

用法: exists后面一定是子查询语句,不能用(值1,值2)代替;where exists (查询),结构中没有列;exists后面的子查询不返回任何实际数据,只返回真或假,当返回真时 where条件成立,该条记录保留。
exists (查询),只要子查询不会空 则where条件就返回真。

免责声明
本博客部分内容来自于互联网,不代表作者的观点和立场,如若侵犯到您的权益,请联系[email protected]。我们会在24小时内进行删除。