mysql的check约束在当前mysql版本中依然是个摆设(mysql版本5.7.9)
那么要怎么取现救国,实现类似check约束的功能呢?
解决方案:
1.使用触发器,来完成类似check的约束验证
2.使用set或enum方式来完成字符串的值范围check约束
//1.set的效果
mysql> create table test(
-> id bigint primary key auto_increment,
-> paymethod set('货到付款','支付宝付款')
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> insert into test(paymethod) values('货到付款');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test(paymethod) values('货到付');
ERROR 1265 (01000): Data truncated for column 'paymethod' at row 1
在java程序中可以读取相关信息:
package com.xie.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class Test {
public static void main(String[] args){
try {
Class.forName("com.mysql.jdbc.Driver");
Connection ct=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/shop","root","centre");
PreparedStatement ps=ct.prepareStatement("select * from test");
ResultSet rs=ps.executeQuery();
while (rs.next()) {
System.out.println(rs.getLong(1));
System.out.println(rs.getString(2));
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}运行结果:
1
货到付款
//2.enum的效果:
mysql> create table test(
-> id bigint primary key auto_increment,
-> paymethod enum('货到付款','支付宝付款')
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test(paymethod) values('货到付款');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test(paymethod) values('货到付');
ERROR 1265 (01000): Data truncated for column 'paymethod' at row 1
测试java代码如上:
测试结果:
1 货到付款