数据库、SQL(MySql/SqlServer/Sqlite/MariaDB/PostgreSQL)、MyBatis/Hibernate
综合
字符长度 - 用LENGTH来计算字段长度避免内容溢出 SELECT CHAR_LENGTH(REPEAT("好", 65535)) -- 65535 按字符而不是字节 -- System.out.println("好".repeat(65535).length()); -- JS: "好".length; UNION SELECT LENGTH(REPEAT("好", 65535)) -- 196605 = 65535 x 3 -- System.out.println("好".repeat(65535).getBytes().length); -- JS: new TextEncoder().encode("好".repeat(3)).length MySQL VARCHAR(入参为字符数而非字节数)在utf8mb4_unicode_ci编码下最大容纳16383字符数,即65535 ÷ 4 = 16383; 而且该字符数为整行总长度,但对于BLOB和TEXT列则只占用数个字节的引用名;故可分为15000存储长文本,1383存储标题等短列。 null值比对必须用IS,而字符串比对则必须用=,解决方案为 - SELECT Name,Sex FROM Article WHERE Sex <=> null IPv4是15个字符;IPv6为39个字符,但HTTP头Forwarded含括号[]为41个字符,故该字段长度取后者。 HAVING支持聚合函数,而WHERE则不支持 - select itemId from tag where name in('hi','latex') GROUP BY itemid HAVING count(itemId) > 1 MyBatis - 传入表名用${table},传入参数用#{p}。 MyBatis 自定义 TypeHandler 可控制DB和Java间的类型转换。 MyBatis和JDBC均会将MySQL的TINYINT(1)转换为true/false,且支持真假判断 WHERE tinyint1 = TRUE,不建议用还需要函数转换的BIT(1)。 PostgreSQL性能和功能支持好于MySQL。
SQL
SQLite
常用命令:
MySql/MariaDB导出数据 - SELECT * FROM t INTO OUTFILE '/tmp/t.txt'
最佳实践
示例模板
MariaDB\MySQL: MySql数据库: 字段长度: Id=36(UUID); Name=50(名称/昵称) 常用列名: 名字 - Name; 创建时间 - When; 内容 - Data; 用户 - User 数据表模板: CREATE TABLE `device` ( `Id` VARCHAR(36) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci', `Name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci', `OtherId` VARCHAR(36) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci', `DemoType` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci', `CreationTime` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB ; CREATE TABLE `config` ( `K` VARCHAR(50) NOT NULL, `V` VARCHAR(255) NULL DEFAULT NULL, `Content` VARCHAR(15000) NULL DEFAULT NULL, `Name` VARCHAR(50) NULL DEFAULT NULL, `Description` VARCHAR(255) NULL DEFAULT NULL, PRIMARY KEY (`K`) ) COLLATE='utf8mb4_general_ci' ENGINE=InnoDB;
Java JDBC
JDBC(Java Database Connectivity) - 关系型数据库管理技术入门
// 若用了mariadb-java-client,则必须修改URL协议为 jdbc:mariadb://... String url = "jdbc:mysql://127.0.0.1:3306/db-name"; String user = "root", password = "1"; try (var conn = DriverManager.getConnection(url, user, password); Statement sta = conn.createStatement()) { // 或 参数化 var ps = conn.prepareStatement(sql); // ps.setString(1, name); // index从1数起 ResultSet rs = sta.executeQuery("select 'Hi'"); // ps.executeUpdate(); if(rs.next()){ System.out.println(rs.getObject(1)); } // Statement关闭时会连带关闭ResultSet } catch (SQLException ex) { System.err.println(ex); } // 今日统计 - select COUNT(ID) AS count FROM article WHERE TO_DAYS(AccessTime)=TO_DAYS(NOW()) // 或 Spring 6.1 JdbcClient API:jdbcClient.sql(sql).param(grade).param(state).query(new StudentRowMapper()).list(); 数据映射: MyBatis将数据库类型 TIMESTAMP(3) 与Java类型 OffsetDateTime 对应。 数据库类型TIMESTAMP存储的是UTC时间戳,写入和读取均会自动转换。