Master the Enum data type in Mysql in one article

This article brings you relevant knowledge about mysql, which mainly sorts out the issues related to the Enum data type. The enum type in Mysql is what we often call the enumeration type. Its The value range needs to be explicitly specified through enumeration (listed one by one) when creating the table. Let's take a look at it. I hope it will be helpful to everyone.

Recommended learning: mysql video tutorial

The enum type in Mysql is what we often call the enumeration type, and its The value range needs to be specified explicitly through enumeration (listed one by one) when creating the table. Enumerations from 1 to 255 members require 1 byte of storage; for members from 255 to 65535, 2 bytes of storage are required. A maximum of 65535 members is allowed.

The bottom layer of enum stores decimal integers, which are strictly arranged in the order 1, 2, 3, 4, 5... Never use enum to store numbers.

Sometimes you can use enumerations instead of commonly used string types. Enumeration columns can store some non-repeating strings into a predefined set. When MySQL stores enumerations, Very compact, compressed into 1 or 2 bytes depending on the number of list values. MySQL internally saves the position of each value in the list as an integer, and saves the "lookup table" of the "number-string" mapping relationship in the .frm file.

Feature verification

New table

CREATE TABLE `mytest` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sex` enum('男','女') DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;

Insert data

insert into mytest(sex) values('男');  // sex = 男
insert into mytest(sex) values('女');  // sex = 女
insert into mytest(sex) values(1);     // sex = 男
insert into mytest(sex) values('1');   // sex = 男
insert into mytest(sex) values('未知'); // 插入报错，1265 - Data truncated for column 'sex' 
insert into mytest(sex) values('0');   // sex = ''
insert into mytest(sex) values(0);     // 插入报错，1265 - Data truncated for column 'sex'

As can be seen from the above, the enum value in the field is similar to the data structure in mysql In an array, and the subscript is calculated starting from 1, it is concluded that:

1. It can be inserted into the database through the correct enumeration value, such as: statements 1 and 2;

2. It can be inserted into the database through the correct subscript, such as: No. 3 and 4 statements;

3. Inserting '0' will be saved as an empty string, such as: No. 6 statements;

4. When inserting number 0 or 'unknown', an execution error will occur, such as statements 5 and 7.

Query data

When querying, you can get the positional relationship of the changed value in the enumeration through "column name 0", as shown in the following SQL statement execution results

SELECT id,sex,sex+0 from `mytest` ;

Query results As follows:

Modify the enumeration

Reduce the enumeration type

UseALTER TABLE mytest MODIFY COLUMN sex enum('male '); statement can reduce the enumeration type. In special cases, if the enumeration value has been used in the record row, a [1265] Data truncated for column error will occur.

Modify the enumeration name

The same as reducing the enumeration type, you must ensure that the enumeration value is not used by the record line

Insert the enumeration in the header

Use the ALTER TABLE mytest MODIFY COLUMN sex enum('unknown','male','female'); statement to insert an enumeration in the header. Inserting an enumeration in the ** header will cause the positional relationship of the enumeration to change. **Inserting an enumeration at the head will cause the relationship between the enumeration and the position to move backward.

As shown below, the result before insertion:

As shown below, the result after insertion:

Incorporate enumeration in the middle

Use ALTER TABLE mytest MODIFY COLUMN sex enum('Male','Unknown','Female');Statement in the middle Insert an enumeration. Inserting an enumeration in the middle of ** will cause the positional relationship of the enumeration to change. **Inserting an enumeration in the middle will cause the relationship between the enumeration and the position after the insertion position to move backward.

As shown below, the result before insertion:

As shown below, the result after insertion:

Insert enumeration at the tail (recommended)

Use ALTER TABLE mytest MODIFY COLUMN sex enum('male','female','unknown'); statement to insert an enumeration at the end . Inserting an enumeration at the end of ** will not cause any change in the relationship between the enumeration value and position. **Since there are no changes, the picture will not be shown here.

Traps in using enumeration types

1. It is not recommended to set a certain field type to enum in mysql, but the stored value is a number, such as '0' , '1', '2';

• ##Explanation 1: will cause confusion because enum can take the value through the subscript, but its subscript is from 1Start, for people who are not familiar with this field, there will be errors

• 解释2： enum类型的字段对于0与‘0’有非常大的区别，如果你是用0当角标做操作，因它没有这个角标，所要会报错；如果你使用‘0’这个值去取枚举值，并做插入操作，你会发现它竟然会成功，但是插入的结果是一个“空”（不是null）

总之，不要拿mysql的enum类型取存一些数字；如果你一定要使用这个字段去存数字，请把这个字段定义为int，然后在java代码中使用枚举类做一个对于这个字段值范围的一个限定！（后面有代码）

2、可能会出现Caused by: java.sql.SQLException: Data truncated for column 'Color' at row 1 ；错误

原因：

• Jpa默认使用整数顺序值持久化枚举类型；

• Mysql中枚举类型Color定义取值的顺序是RED、GREEN、BLUE，因此，当这三个取值持久化到数据库表时，取值分别是0、1、2；

• 意思就是我们这里存往数据库的数据是0、1、2这样的数字，而不是RED、GREEN、BLUE字符串，但是Mysql数据库中定义的是RED、GREEN、BLUE，并没有其它值所以报错

解决:在entity中使用@Enumerated(EnumType.STRING)标注你的枚举类型属性，如果标注，默认是integer。

枚举使用例子

在JPA中使用@Enumerated(EnumType.STRING)，这种是推荐的方法。

建表语句

 CREATE TABLE test4 (  
     id BIGINT UNSIGNED  PRIMARY KEY AUTO_INCREMENT,  
     brand VARCHAR(255) NOT NULL,  
     color ENUM('RED','GREEN','BLUE')
  ) ENGINE = InnoDB;

Java代码中，枚举类

public enum Color {
  RED,  
  GREEN,  
  BLUE  
}

Java代码中，Javabean

@Entity  @Table(name="test4") public class ClothesRight {
 @Id  
    @GeneratedValue(strategy = GenerationType.IDENTITY)  
    private Long id;  
 
 @Enumerated(EnumType.STRING)
    private Color color;  
    
    private String brand;
 
 public Long getId() {
  return id;
 }
 
 public void setId(Long id) {
  this.id = id;
 }
 
 public String getBrand() {
  return brand;
 }
 
 public void setBrand(String brand) {
  this.brand = brand;
 }
 
 public ClothesRight(Long id, Color color, String brand) {
  super();
  this.id = id;
  this.color = color;
  this.brand = brand;
 }
 
 public Color getColor() {
  return color;
 }
 
 public void setColor(Color color) {
  this.color = color;
 }
 
 public ClothesRight() {
  super();
 }
    }

简单使用

public interface Test4RightRepository extends JpaRepository<clothesright>{
 }</clothesright>

@Autowired
 private Test4RightRepository t4R;/**
  * 使用@Enumrated()标注字段为枚举的数据
  * 结果 正确插入RED
  */
 @GetMapping(value="/addclothesright")
 public void GetTest4Right(){
  List<clothesright> entities = new ArrayList();
  ClothesRight clothes = new ClothesRight();  
  //clothes.setId(1L);
  clothes.setBrand("佐丹奴");  
  clothes.setColor(Color.RED); 
  entities.add(clothes);
  t4R.save(entities);
 }</clothesright>

结果为：

插入数字例子（不推荐）

mysql枚举的字段类型不宜插入数字，但是需求就是要用数字，怎么办？

解决：mysql数据类型定义为int,枚举限定在java代码中解决

建表语句

CREATE TABLE test5num (  
 id BIGINT UNSIGNED  PRIMARY KEY AUTO_INCREMENT,  
  used int(11) DEFAULT NULL COMMENT '0:没用过  1:已用过  2:不能用'
 )ENGINE = InnoDB;

Java代码

@Entity@Table(name="test5num")public class Test5Num {
 @Id
 @GeneratedValue(strategy=GenerationType.IDENTITY)
 private Long id;
 
 private Used used;
 
 public Long getId() {
  return id;
 }
 
 public void setId(Long id) {
  this.id = id;
 }
 
 public Used getUsed() {
  return used;
 }
 
 public void setUsed(Used used) {
  this.used = used;
 }
 
 public Test5Num() {
  super();
 }
 
 public Test5Num(Long id, Used used) {
  super();
  this.id = id;
  this.used = used;
 }
 }

/**
*枚举类
*/public enum Used {
 UNUSED(0,"没用过"),
 USED(1,"已用过"),
 FORBIDDEN(2,"不能用");
 
 private Integer code;
 private String discribe;
 public Integer getCode() {
  return code;
 }
 public String getDiscribe() {
  return discribe;
 }
 private Used(Integer code, String discribe) {
  this.code = code;
  this.discribe = discribe;
 }}

/**
 * dao层
 */public interface Test5NumRepository extends JpaRepository<test5num>{
 }</test5num>

@Autowiredprivate Test5NumRepository t5N;
     /**
 * mysql枚举的字段类型不宜插入数字，但是需求就是要用数字，怎么办？
 * 解决：mysql数据类型定义为int,枚举限定在java代码中解决
 * 
 */@GetMapping("/test5insert")public void insertT5(){
 Test5Num t5 = new Test5Num();
 t5.setUsed(Used.USED);
 List<test5num> list = new ArrayList<test5num>();
 list.add(t5);
 t5N.save(list);}</test5num></test5num>

小结

• 可以使用enum类型存放例如订单状态、用户性别等数据类型，mysql底层会对枚举值进行压缩并按照枚举顺序转换成10进制顺序位存储。

• 程序在保存enum字段数据时，直接保存enum字符串。要注意0和'0'的区别。

• enum定义完成以后不要随便删除，也不对对已经在使用的enum值进行修改。

• 使用alert语句修改enum的规范是后面添加而不是在首部后者中间插入或者修改。

推荐学习：mysql视频教程

The above is the detailed content of Master the Enum data type in Mysql in one article. For more information, please follow other related articles on the PHP Chinese website!