这个是对Java开发人员的MySQL使用手册.乱序持续更新中,建议不要使用5.6以下的版本,至少5.7以上.
MySQL的数据类型。MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。这个一定要熟悉,因为关系到你设计的表是否合理.可以参考MySQL 数据类型.字符集不要设置为utf8 ,改为utf8mb4.COLLATE使用utf8mb4_general_ci金额字段使用 int 或者 bigint 类型。如果需要存储到分的维度，需要 *100 进行放大.或者使用 decimal 类型，避免精度丢失。如果使用 Java 语言时，需要使用 BigDecimal 进行对应. FLOAT 和 DOUBLE 都是不精确的，如果要实现精确浮点运算，不能选择FLOAT 和 DOUBLE。如果主键索引是业务索引字段,并且长度达到十几,比如身份证号,建议使用自增主键索引,然后给你的业务字段创建普通索引查询所有有自增字段的表select * from information_schema.`COLUMNS` where EXTRA='auto_increment' and TABLE_SCHEMA='payment'like 的方式进行查询，在 like “value%” 可以使用索引，但是对于 like “%value%” 这样的方式，执行全表查询。使用了复合索引时，遵守“最左前缀”原则，即在查询条件中使用了复合索引的第一个字段，索引才会被使用。因此，在复合索引中索引列的顺序至关重要。DATETIME 和 TIMESTAMP 都可以存储相同类型的数据，而 TIMESTAMP 只使用 DATETIME 一半的存储空间。通常情况下，建议优先考虑 TIMESTAMP，因为它的空间利用率更高。用法@Temporal(TemporalType.TIMESTAMP) @Temporal(TemporalType.TIMESTAMP) @Column(name = "trade_time") // "交易时间") private Date tradeTime;MySQL数据类型尽量不要使用枚举类型。如果需要增加新的枚举类型，需要全表更新，对于大数据量的更新场景，会造成锁表
定长的字符串，可以选择 CHAR。举个例子，身份证是定长类型，那么选择 CHAR 是非常合适的。
变长的字符串，可以选择 VARCHAR， 因为 VARCHAR 更加节省空间。
存储大文本的场景，可以考虑使用 BLOB 和 TEXT。
整数类型可以选择 UNSIGNED 属性，表示不允许负数，这样可以使得正数的上限提高一倍。
尽量避免使用 != 或 not in或 <> 等否定操作符。(在mysql8.0.19中not in 不会使索引失效)
尽量避免使用 or 来连接条件。
查询中的某个列有范围查询，则其右边所有列都无法使用索引优化查找。
复合索引中只要有一列含有 NULL值，那么这一列对于此复合索引就是无效的。(mysql8.0.19也能使用索引)
不要在列上使用函数和进行运算.
对于同时存在 WHERE 和 ORDER BY 的 SQL 语句，可以建立一个复合索引进行性能优化.
前缀索引就是将字段前缀的一部分作为索引。前缀索引有利于节省空间，也有利于提高查询速度，但是也有弊，1.不能使用覆盖索引了2.增加查询次数(前缀索引的目的是先初步筛选出id，然后再查询完整数据再对比是否满足条件)。3.不支持范围查询。前缀索引的使用方式：alter table SUser add index index2(email(6));
倒序存储就是将字段内容倒序存入数据库。字段内容区分度不够时使用倒序存储能增加查询效率。缺点是只支持常规等值查询
hash字段索引就是创建一个hash字段，存储索引字段内容的hash值。缺点是不能使用范围查询。使用时查询方式为：select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'
查看表字段所有信息show full columns from company_info; 或者 desc xxxxxx;
查询表创建信息:show create table xxxxxx;
最左前缀原则: 复合索引 a,b,c 那么有效的where 只有三个,含有a,含有a,b 含有 a,b,c.如果不含a,一定不走索引.
Java中的boolean类型,或者小于255的数, 推荐使用byte或者tinyint存储.
示例1: detail.setDealType(Byte.valueOf(String.valueOf(dealType))); /** * 交易类型,1充值 2发工资3退款 4提现 5转入 6转出 7众包结算 8还款 */ @Column(name = "deal_type") private Byte dealType; <result column="deal_type" property="dealType" jdbcType="TINYINT" /> 示例2: @Column(name = "is_system_") private Boolean isSystem; <result column="is_system_" jdbcType="TINYINT" property="isSystem"/>如下是本人在最新MySQL8.0.19版本中测试结果:
DROP TABLE IF EXISTS `Student`; CREATE TABLE `Student` ( `id` int(0) UNSIGNED NOT NULL AUTO_INCREMENT, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL, `age` int(0) UNSIGNED NULL DEFAULT NULL, `memo` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL, `classes` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL, `school` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, INDEX `name_index`(`name`) USING BTREE, INDEX `age_index`(`age`) USING BTREE, INDEX `class_school_index`(`classes`, `school`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_bin ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of Student -- ---------------------------- INSERT INTO `Student` VALUES (1, 'johnny', 29, 'test', 'nanshan', 'NanShanSCHOOL1'); INSERT INTO `Student` VALUES (2, 'HUANG', 34, 'HUANGTEST', '', 'NanShanSCHOOL2'); INSERT INTO `Student` VALUES (3, 'Johnny', 23, 'test', 'nanshan', 'NanShanSCHOOL3'); INSERT INTO `Student` VALUES (4, 'Johnny1', 21, 'sdf ', NULL, 'NanShanSCHOOL3');表有4个索引,其中一个是主键索引,一个是复合索引.
PRIMARY KEY (`id`) USING BTREE, INDEX `name_index`(`name`) USING BTREE, INDEX `age_index`(`age`) USING BTREE, INDEX `class_school_index`(`classes`, `school`) USING BTREE
EXPLAIN SELECT * FROM Student WHERE NAME LIKE 'Johnny%'; -- 走索引 EXPLAIN SELECT * FROM Student WHERE NAME LIKE '%Johnny%'; -- 不走索引 EXPLAIN SELECT * FROM Student WHERE LEFT(NAME,2) = 'Jo'; -- 对列使用了函数不走索引 EXPLAIN SELECT * FROM Student WHERE NAME = 1; -- 字符串与数字比较,不走索引 EXPLAIN SELECT * FROM Student WHERE NAME = '1'; -- 走索引 EXPLAIN SELECT * FROM Student WHERE age +1> 23; -- 不走索引,每一行都参与运算 EXPLAIN SELECT * FROM Student WHERE age < 20 or age >60 ; -- 走索引 EXPLAIN SELECT * FROM Student WHERE id = 3; -- 走索引 EXPLAIN SELECT * FROM Student WHERE school = 'NanShanSCHOOL3' and classes = 'nanshan'; -- 走索引 EXPLAIN SELECT * FROM Student WHERE classes = 'nanshan' and school = 'NanShanSCHOOL3' ; -- 走索引 有个列classes有默认的null值也走了索引 EXPLAIN SELECT * FROM Student WHERE classes != 'nanshan'; EXPLAIN SELECT * FROM Student WHERE age < 60 and age >20 ; -- 走索引 EXPLAIN SELECT * FROM Student WHERE age between 20 and 50 ; -- between走索引 EXPLAIN SELECT * FROM Student WHERE age not between 20 and 50 ; -- not between走索引 EXPLAIN SELECT * FROM Student WHERE age in(20, 23) ; -- in走索引 EXPLAIN SELECT * FROM Student WHERE age not in(20, 23) ; -- not in走索引 -- 查询当前数据库版本 select version();MySQL常用数据类型:
1.date:日期 yyyy-MM-dd. jdbcType="DATE",java接收是
@DateTimeFormat(pattern="yyyy-MM-dd") @JsonFormat(pattern = "yyyy-MM-dd") @JSONField(format="yyyy-MM-dd") private Date establishDate;2.datetime 日期时间 yyyy-MM-dd HH:mm:ss .jdbcType="TIMESTAMP"
3.tinyint: 从 0 到 255 的整型数据。存储大小为 1 字节。 可以用来存储标志flag
注意:可以使用Byte 或者int 或者Integer 来对应Java中的类型比如
@Column(name = "deal_type") private Byte dealType;同时,设置值时要注意转换
detail.setDealType(Byte.valueOf(String.valueOf(dealType)));4.decimal 小数值, jdbcType="DECIMAL"/> Java接收是
@Column(name = "pay_real_amount") private BigDecimal payRealAmount;5.BLOB TEXT 二进制长文本数据 和长文本数据
6.CHAR 0-255 bytes 定长字符串 Java可以用String接收
@Column(name = "amount") @ExcelOrder(order = "9",name = "交易金额") private String amount;VARCHAR 0-65535 bytes 变长字符串
