文章目录

1、测试 check 在 mysql 中作用与否 ？

1、 我创建表， 有 check(stock>0) 检查的，但mysql 好像会 把 check 语句直接丢弃？
2、 后面无论 ，是在 mysql 里面 修改数据，还是 在java 里面修改数据 mysql 都不会 执行 check 语句？

2、结论

mysql 官网也说了，会忽略 check 哈哈。。

3、测试的代码

  • create_table_test1.sql 
use test_db ; 
drop table if exists test1  ;  -- 创建新表
create table if not exists test1 (
	id int primary key auto_increment , 
    stock int check(stock > 0 ) 
    ) ; 
desc test1 ; -- 查看表结构
show create table test1; -- 查看建表语句
  • test_db-test1.sql 
insert into test1 values(null , 1 ) , (null , -2 ) ;  
select * from test1 ;
update test1 set stock = -1 ;
select * from test1 ;
  • Test_Check.java 
package com.edut.test.mysql;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.junit.Test;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class Test_Check {
	private Properties properties ; 
	private DataSource dataSource ; 
	private Connection conn ; 
	private QueryRunner queryRunner ; 
	
	{
		try {
			properties = new Properties();
			properties.load(getClass().getResourceAsStream("/c3p0.properties"));
		} catch (IOException e) {
			e.printStackTrace();
		}
		try {
			Class.forName(properties.getProperty("c3p0.driverClass")) ;
			conn = DriverManager.getConnection(
					properties.getProperty("c3p0.jdbcUrl"), 
					properties.getProperty("c3p0.user"), 
					properties.getProperty("c3p0.password"));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		queryRunner = new QueryRunner() ; 
	}
	
	@Test
	public void testProperties() {
		System.out.println("====== testProperties =====================");
		System.out.println("c3p0.driverClass"+properties.getProperty("c3p0.driverClass"));
		System.out.println("c3p0.jdbcUrl:"+properties.get("c3p0.jdbcUrl"));
		System.out.println("c3p0.user:"+properties.get("c3p0.user"));
		System.out.println("c3p0.password:"+properties.get("c3p0.password"));
	}
	
	/* * database:test_db * table:test1 * col:id/stock */
	
	
	public void findAll() throws SQLException {
		String sql = " select * from test1 ; " ;
		List<Object[]> query = queryRunner.query(conn , sql ,new ArrayListHandler());
		for (Object[] objects : query) {
			System.out.println(Arrays.toString(objects));
		}
	}
	
	@Test
	public void testFinAll() throws SQLException {
		System.out.println("====== testFinAll =====================");
		findAll(); 
	}
	
	@Test
	public void testUpdate() throws SQLException {
		System.out.println("====== testUpdate =====================");
		findAll();
		System.out.println("++++++++++++++++++++");
		String sql1 = " update test1 set stock = 2 ; " ;
		queryRunner.update(conn , sql1) ; 
		findAll();
		System.out.println("++++++++++++++++++++");
		String sql2 = " update test1 set stock = -2 ; " ;
		queryRunner.update(conn , sql2) ; 
		findAll();
		
	}
	
}