最新博文

• MySQL序列

  MySQL

  MySQL 序列是一组整数：1, 2, 3, ...，由于一张数据表只能有一个字段自增主键， 如果想实现其他字段也实现自动增加，就可以使用MySQL序列来实现。

  一.AUTO_INCREMENT的限制

  MySQL的自增长约束auto_increment虽然很有用，但在实际开发过程中有很多限制：

  （1）首先mysql的自增长“序列”（auto_increment）和序列（类似于oracle的sequence）并不是一个概念，mysql本身不提供序列机制。 
  

  （2）mysql的AUTO_INCREMENT可以设置起始值，但是不能设置步长，其步长默认就是1. 
  

  （3）mysql一个表只能有一个自增长字段。自增长只能被分配给固定表的固定的某一字段，不能被多个表共用。并且只能是数字型。 
  

  （4）在历史表和数据迁移时，经常会遇到自增主键重复的问题。 
  

  （5）自增主键往往是没意义的。 

  二.需要使用序列的情况

  （1）业务复杂，需要高度定制和控制主键（自增主键只能是按数字递增的，但是序列可以随心所欲的变化，比如按照年月日生成主键。。。）
  

  （2）希望手工维护自增长，方便数据迁移；
  

  （3）当事务跨多表时，期望事务可靠性； 
  

  （4）当需要一个业务上有意义的主键时，比如单据号等（若需要的只是一个流水号，那么用自增更方便）； 
  

  （5）当主键很明确地需要和其他表关联时（若该表不与其他表关联时，如日志表，自增更方便）；
  

  （6）期望这个主键是唯一的，不需要重复利用的。 

  三.序列的缺点

  序列也有缺点，主要就是程序处理麻烦，不如自增方便。oracle的自增有缓存，不用担心效率问题，而mysql只能通过触发器模拟，可能会有性能损失。

  四.如何使用序列

  由于mysql和oracle不太一样，不支持直接的sequence，所以需要创建一张table来模拟sequence的功能。步骤如下：

  一.创建“序列库”（一表两函数）

  1.创建sequence表；

  2.创建取当前值的函数current_val();

  3.创建取下一个值的函数next_val();

  二.实例化“序列库”，

  1.向sequence表中加入初始值定义序列名称和步长

  三.建立触发关系

  1.将某表需要使用序列的字段与序列对象建立触发关系。

   

  具体说明如下：

  （一）建立“序列库”

  第一步：创建--Sequence 管理表

  drop table if exists sequence;

  create table sequence (

  name VARCHAR(50) NOT NULL, -- 序列名称

  current INT NOT NULL, -- 当前值

  increment INT NOT NULL DEFAULT 1, -- 步长(跨度)

  PRIMARY KEY (seq_name)

  );

   

  第二步：创建--取当前值的函数currval()

  create function currval(object_name VARCHAR(50)) returns integer

  begin

  declare value integer;

  set value = 0;

  select current into value from sequence where name = object_name;

  return value;

  end;

   

  第三步：创建--取下一个值的函数nextval()

  create function nextval (object_name VARCHAR(50)) returns integer

  begin

  update sequence set current= current+ increment where name = object_name;

  return currval(object_name);

  end;

   

  （二）实例化序列库

  INSERT INTO sequence VALUES ('seq_name1', '0', '1');

  #实例化出一个名称为"seq_name1"，步长为1的序列

  INSERT INTO sequence VALUES ('xiaoming', '0', '2');

  #实例化出一个名称为"xiaoming"，步长为2的序列

  （三）建立约束关系

  我们先建一个test1表

  DROP TABLE IF EXISTS  test1 ;

  CREATE TABLE  test1  (

   name  varchar(255) NOT NULL,

   value  double(255,0) DEFAULT NULL,

   num1  int(11) DEFAULT NULL,

   num2  int(11) DEFAULT NULL,

  PRIMARY KEY ('name')

  );

  建立触发关系

  CREATE TRIGGER 'TRI_num1' BEFORE INSERT ON 'test1' FOR EACH ROW

  BEGIN

  set NEW.num1 = nextval('seq_name1'); #表test1的num1字段和seq_name1建立触发关系

  set NEW.num2 = nextval('xiaoming'); #表test1的num2字段和xiaoming建立触发关系

  END

   

   

  我们来测试一下

  空表test1

  

  向空表非使用序列字段name和value插入数据，观察使用序列的num1和num2的变化。

  INSERT INTO test1 (name, value) VALUES ('1', '111');

  INSERT INTO test1 (name, value) VALUES ('2', '222');

  INSERT INTO test1 (name, value) VALUES ('3', '333');

  INSERT INTO test1 (name, value) VALUES ('4', '444');

  查看一下结果

  SELECT * FROM test1;

  

  从结果中我们可以看到，num1实现了以步长为1的增长，num2实现了以步长为2的增长

  风口下的猪2019-06-09【MySQL】

  阅读更多

• MySQL存储函数和存储过程的区别

  MySQL

  一、 存储函数有且只有一个返回值，而存储过程不能有返回值。

  二、 函数只能有输入参数，而且不能带in, 而存储过程可以有多个in,out,inout参数。

  三、 存储过程中的语句功能更强大，存储过程可以实现很复杂的业务逻辑，而函数有很多限制，如不能在函数中使用insert,update,delete,create等语句；存储函数只完成查询的工作，可接受输入参数并返回一个结果，也就是函数实现的功能针对性比较强。

  四、 存储过程可以调用存储函数。但函数不能调用存储过程。

  五、 存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调用。

  风口下的猪2019-06-09【MySQL】

  阅读更多

• MySQL—存储函数

  MySQL

  作为维持数据库完整性特征的主要方法之一，函数和存储过程一样，举足轻重。它和存储函数极为相似，但也有自己不同的地方。MySQL函数主要包括内置函数和自定义函数两个部分。

  一.内置MySQL函数

  内置函数的调用一律使用select 函数名()的形式调用函数。

  其主要分类如下：

  （1）字符串函数

  （2）数学函数

  （3）日期时间函数 

  （4）转换函数

  开发过程中详情参靠

  1.菜鸟教程MySQL函数说明：https://www.runoob.com/mysql/mysql-functions.html

  2.某博主整理：https://www.cnblogs.com/xuyulin/p/5468102.html

  二.MySQL自定义函数UDF

  自定义函数 (user-defined function UDF)就是用一个象ABS() 或 CONCAT()这样的固有（内建）函数一样作用的新函数去扩展MySQL。

  所以UDF是对MySQL功能的一个扩展

  （1）语法结构

  DELIMITER//

  DROP FUNCTION IF EXISTS 函数名称;

  CREATE FUNCTION 函数名称(参数列表)RETURNS 返回值类型

  BEGIN

  　　函数体;

  END//

  DELIMITER;

  例子一（无参存储函数）：

  A.定义无参函数

  DELIMITER//

  drop function if exists hello;

  create function hello () returns varchar (255)

  begin

  return '一个简单的mysql函数';    #函数的返回值

  end//

  DELIMITER;

  B.调用无参函数

  select hello();

  C.调用结果

  hello()

  一个简单的mysql函数

  例子二（带参存储函数）：

  A.创建带参存储函数（此例是将字符拼接成一个字符串返回给函数）

  DELIMITER//

  drop function if exists hello;

  create function hello(a varchar(20),b varchar(20)) returns varchar (255)

  begin

  declare x varchar(255) default ' x ';

  declare y varchar(255) default b;

  declare c varchar(255) default ' 2017-01-02 ';

  declare d varchar(255);

  set d = concat(x,a,b,y,c);

  return d;

  end//

  DELIMITER;

  B.调用函数

  select hell('a','b');

  C.调用结果

  hello()

  x a b b 2017-01-02

  （2）注意点：

  1.存储函数有且只有一个返回参数无论带参或者不带参。参数的定义分两个部分：一是定义返回参数数据类型，其在create函数后面。其二是返回具体结果（可是变量、字符串），其在函数体中；

  2.存储函数和存储过程一样，可以在函数体中使用判断、条件、循环等控制语句；

  3.函数调用，虽然可以直接写函数名调用，但这样不会有任何结果。所以存储函数的调用，一律使用select；

  4.存储函数有受限条件:不能在函数中访问表

  因此在函数中使用以下语句是非法的。
  ALTER 'CACHE INDEX' CALL COMMIT CREATE DELETE
  DROP 'FLUSH PRIVILEGES' GRANT INSERT KILL
  LOCK OPTIMIZE REPAIR REPLACE REVOKE
  ROLLBACK SAVEPOINT 'SELECT FROM table'
  'SET system variable' 'SET TRANSACTION'
  SHOW 'START TRANSACTION' TRUNCATE UPDATE

  （3）查看存储函数

  • 查看函数创建语句：show create function 函数名;
  • 查看所有函数：show function status [like 'pattern'];

  （4）修改存储函数

  函数的修改只能修改一些如comment的选项（存储函数注释），不能修改内部的sql语句和参数列表。

  alter function 函数名 注释；

  （5）删除存储函数

  drop function 函数名;

  风口下的猪2019-06-09【MySQL】

  阅读更多

• MySQL存储过程—-判断、条件、循环语句

  MySQL

  MySQL存储过程体简直就是函数体，所以，存储过程体中也必然包含判断、条件、循环等语句。

  理解和掌握MySQL存储过程各语句的核心是要记住一点：MySQL没有括号和花括号

  一.判断

  （1）IF语法

  IF 条件 THEN

  SQL语句；

  ....

  END IF

  例如：

  DELIMITER //

  CREATE PROCEDURE test1(IN m int)

  BEGIN

  IF m>0 THEN

  update users set money=money+10 where id=2;

  END IF

  END//

  DELIMITER;

  （2）IF--ELSE语法

  IF 条件 THEN

  SQL语句；

  ......；

  ELSE

  SQL语句；

  .......；

  END IF

  例如：

  DELIMITER//

  CREATE PROCEDURE test2 (IN m int)

  BEGIN

  IF m<20 THEN

  update users set money=money+m where id=10;

  ELSE

  select'亲，输入金额有误' as 'title';

  END IF

  END/

  DELIMITER;

  （3）IF-ELSE-IF语法

  IF 条件 THEN

  SQL语句；

  ......；

  ELSEIF 条件 THEN

  SQL语句；

  .......；

  END IF

  例如：

  DELIMITER//

  CREATE PROCEDURE test3()

  BEGIN

  declare m int

  set m=select money from user where id=5;

  if m>500 then

  select'买保时捷' as 'canbuy';

  elseif m>300 then

  select'买宝马' as 'canbuy';

  elseif m>10 then

  select'买奥拓'as 'canbuy';

  end if

  END//

  DELIMITER;

  二.条件（case）

  条件不像java或者php中使用swith，MySQL存储过程体使用的是case-when结构

  我们先来看看java和php的swith

  swith(int i){

  case 1:

  System.out.println('我是情况一');

  break;

  case2:

  System.out.println('我是情况二');

  break;

  default:

  System.out.println('我是情况三');

  break;

  }

  再来看看MySQL存储体中的条件语句语法

  CASE 条件变量

  WHEN 条件1 THEN

  SQL语句；

  .....；

  WHEN 条件2 THEN

  SQL语句；

  ......；

  ELSE

  SQL语句；

  .....；

  END CASE

  例如：

  DELIMITER//

  CREATE PROCEDURE test4( IN i int)

  BEGIN

  case i

  when 1 then

  select '星期一' as '日期';

  when 2 then

  select '星期二' as '日期';

  when 3 then

  select '星期三' as '日期';

  else

  select '还不是时候' as '日期';

  end case

  END//

  DELIMITER;

  三.循环

  （1）while循环

  语法结构：

  WHILE 条件 DO

  SQL语句；

  .......；

  END WHILE

  例如（向users表中插入100条数据）：

  DELIMITER//

  CREATE PROCEDURE test6(IN i int)

  BEGIN

  declare a int default 100;   #此处采用存储变量申明及初始化连贯操作

  while a<i do

  insert into 'users' set username='test6',money=100;

  set a=a+1;

  end while;

  END//

  DELIMITER;

  （2）loop循环

  语法结构：

  循环名:LOOP

  SQL语句；

  .....；

  LEAVE 循环名

  END LOOP

  例如（实现从1加到100的总和）：

  DELIMITER//

  CREATE PROCEDURE test7( OUT num INT)

  BEGIN

  declare i int default 0;

  re_name1: loop

  set i=i+1;

  if i=100 then

  set num=i;

  reave re_name;

  end if

  end loop

  END//

  DELIMITER;

   

  #调用时

  call test7(@resualt);

  select @resualt;

  风口下的猪2019-06-09【MySQL】

  阅读更多

• MySQL存储过程—申明变量以及变量初始化

  MySQL

  我们知道存储过程类似于中间层语音的函数体。那么既然是MySQL中的函数体，则必然有参数列表和变量的申明及初始化。

  一.参数列表中形参的顺序与java不同

  我们知道java的参数列表顺序是先数据类型再参数名，即int i，但是MySQL则不同，其是先参数名，再数据类型。例如

  java函数体: public void learn( int a,float des)

  MySQL存储过程：CREATE PROCEDURE learn( a int, des float)

  二.在MySQL存储过程中申明变量

  （1）函数参数列表中未申明时

  使用DECLARE关键字

  eg:

  DELIMITER//

  CREATE PROCEDURE pro_loop(IN i INT)

  BEGIN

  DECLARE a INT

  END//

  DELIMITER;

  （2）参数列表中申明

  CREATE PROCEDURE pro_loop(IN i INT)

  三.变量的初始化

  （1）对参数列表中申明了的变量进行初始化

  CREATE PROCEDURE pro_loop(IN i INT)

  BEGIN

  SET i=100;

  END

  （2）对参数列表未申明的变量初始化

  DECLARE  a INT;

  SET a=10;

  或者申明实例化连贯操作

  DECLARE a INT DEFAULT 10;

  风口下的猪2019-06-09【MySQL】

  阅读更多

• MySQL存储过程–基本规则

  MySQL

  mysql的存储过程，是一组sql语句集合，其相当于java中的函数，可以传参和不传参。先申明存储过程名（相当于函数），然后执行调用。其跟着数据库，不像一般的sql语句执行完便消失。

  存储的优点：

  • 跟着数据库走，有一定内存空间，想调用时便可调用，便于重复造车轮；
  • 安全性好，适合金融行业，在金融行业中使用MySQL存储很普遍；
  • 增强了mysql的功能，使业务逻辑变得更加具有现实意义和强大。

  注意点：

  MySQL 5.0以前并不支持存储过程，这使得MySQL在应用上大打折扣。好在MySQL 5.0开始支持存储过程，这样即可以大大提高数据库的处理速度，同时也可以提高数据库编程的灵活性。

  一.不带输出参数语法结构：

  DELIMITER//        #定义以//代替;作为新的分隔符

  CREATE PROCEDURE 存储过程名 （参数名1 参数1类型,参数名2 参数2类型）

  LANGUAGE SQL  #存储过程的执行语句，这里定义的是SQL

  DETERMINISTIC  #当存储过程输出和输出内容相同时，使用其申明

  SQL SECURITY DEFINER   #调用时，检查用户的使用权限

  COMMENT 'A procedure'  #存储过程的注释内容

  BEGIN

  sql语句1;

  sql语句2;

  .......;

  END//

  DELIMITER;     #还原;作为分隔符

  例子如下：

  delimiter//

  create procedure test1(money float,idd bigint)

  bigin

  update 'emp' set salary=salary+money where id=idd;

  end//

  delimiter;

  对比一下java函数

  public volid salary( float money,bigint idd){

  float salary;

  salary=salary+money;

  }

  （1）delimiter：用于申明分割符是什么，为什么在存储过程中强调使用demiliter，是因为sql语句的结尾是；，执行到;便结束，也就是说不会执行到end（相当于方法体的花括号）。所以，必须在使用存储过程时修改;，将其用于存储过程末尾表明整个存储过程是一个整体，并在执行完后还原分割符为;（不还原其他sql语句怎么执行？要有这个习惯）；

  （2）begin和end相当于一对花括号，用于盛放一组sql语句；

  （3）与java函数不同的是，注意参数名称和其数据类型的位置顺序，java函数是int id，MySQL存储过程是id int。

  二.带输出参数语法结构

  DELIMITER //

  CREATE PROCEDURE 存储名称 (IN 参数名1 参数1类型，IN 参数名2 参数2类型，OUT 参数名3 参数3类型)

  BEGIN

  sql语句;

  .......

  END

  DELIMITER;

  CALL '存储名称' (参数1,参数2,@返回参数变量)  #相当于tp5中的$this->assign()

  SELECT @返回参数变量

  例子如下：

  #申明存储过程体

  delimiter//

  create procedure test2(IN j float,IN k float,OUT num float)

  begin

  set num=j+k;

  end//

  delimiter;

  #调用存储过程并传参，并返回结果到结果变量@resualt中

  call 'test2'(10,20,@resualt)

  #使用结果变量

  select @resualt

  （1）带参输出时，注意传参的参数列表，传入参数使用IN前缀修饰，传出参数使用OUT前缀修饰；

  （2）call '存储名称'(参数1,参数2,@resualt)是存储过程的回调执行，意思是将结果赋值给@resualt变量，相当于tp5中的$this->assign()将值赋值到模板变量中；

  （3）select:使用存储结果变量

  三.删除存储过程

  drop procedure 存储过程名

  四.修改存储过程

  alter procedure 存储过程名

  风口下的猪2019-06-09【MySQL】

  阅读更多

• MySQL事务

  MySQL

  MySQL 事务主要用于处理操作量大，复杂度高的数据。它通过将多组sql语句并发执行，并与其它事务互不干扰，形成具有实际意义的事件，例如“转账、财务核算、人员资料管理”等。

  只有有使用了 Innodb 数据库引擎的数据库或表才支持事务。

  一.定义

  事务具有三层意义：

  1.是多组数据在一个事务中同时并发进行；

  2.多个事务执行相互具有独立性，应互不干扰；

  3.事务的正确性不取决于本身，而取决于应用场景。

   

  二.事务的特性

  一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）

  1.原子性（Atomicity）：在it行业，原子即不能再分割了，也就是说，虽然事务下面包含很多sql语句，但是一个事务的最小单位就是事务，不能再分割成各个语句。

  2.一致性（Consistency）：事务内的各个sql语句应是并发执行，执行有效无效保持一致，不能一条sql语句执行成功了另一些sql语句执行失败了，都应该一致，要么都失败要么都成功。

  3.隔离性（Isolation）：隔离性针对事务与事务之间，每个事务内部的sql语句执行，应该不影响其他事务的执行。

  隔离性有四种隔离级别（其是数据库的隔离级别，以下隔离级别是依次递增）

  （1）read uncommitted（读未提交）

  （2）read committed（读提交）

  （3）repeatable read（可重复读）

  （4）Serializable（串行化）

  4.持久性（Durability）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

   

  三.事务的语法

  事务的语法主要是

  start transaction(或者begin)

  sql语句1；

  sql语句2；

  sql语句3；

  ...........

  commit(或者commit work)

  以上是一个完整的事务，在实际情况中，可多个事务同时进行，其应该不相互干扰；

  当然，除了start transaction和commit以外还有其他控制语句：

   

  • rollback：有可以使用rollback work，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
  • savepoint identifier：savepoint允许在事务中创建一个保存点，一个事务中可以有多个savepoint；
  • release savepoint  identifier：删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
  • rollback to identifier：把事务回滚到标记点；
  • set transaction：用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有四个。

  四.隔离详解（事务的主要体现点）

  首先，我们要明白，隔离在数据库实现业务功能时不可或缺，但隔离级别并不是越高越好。

  有一个规律：数据库隔离级别越高，其性能越差。（综合考虑，实际开发常用的隔离级别是read committed）

  1.查看mysql隔离级别

  selet @@tx_isolation

  2.修改mysql隔离级别

  set global transaction isolation level 级别名

  例如：set global transaction isolation level repeatable

   

   

  3.来看看各种隔离级别会出现的情况以及应对办法

  （1）当隔离级别为read uncommitted时，会引发“脏读”

  脏读：一个事务可以读取另一个事务还未提交的数据。

  下面从一个实际事件来理解：李四向张三还款50w

  

  

  此时的张三查看到自己的信息表如下：

  

  张三高兴坏了，打了一年的欠条，终于把钱收回来了。

   

  但是此刻，李四使坏，在没有执行commit之前，执行两次回滚，再递交。

  

  此时的张三查看到自己的信息表如下：

  

  张三傻眼了，自己的50w哪里去了，之前明明账户里有80w。

  事件总结说明：张三的事务二，读取了李四还没有递交的事务一。

  如何解决脏读？

  将mysql隔离级别改为read committed

  执行sett global transaction isolation level read committed

  解决后的情况是：李四在没有执行commit前，做了update余额转账，叫张三查看余额，张三看到只有30w。只要没有递交，无论李四对money字段如何增删改，张三都只能看到30w。

   

  （2）当隔离级别为read committed时，会引发不可重复读

  不可重复读：在一个事务中多次读取数据（本事务未执行commit），而另一些事务对数据进行了增删改干扰，使这一个事务中多次读取数据的结果不同。

  下面从一个实际事件来理解：年末全国城市财政经济普查

  

  

  

  

  

  我们发现，在事务一中，总经办各部门查询的时段里，各城市的财政状况发生着改变，使得总经办各部门得到的数据并不相同。

  事件总结说明：在总经办多次执行查询的事务当中，各地财政变化执行多个事务，对总经办的查询造成了干扰。

  如何解决不可重复读？

  将mysql隔离级别改为repeatable read

  执行sett global transaction isolation level repeatable read

  解决后的情况是：尽管总经办查询事务一有没有commit递交，各地财政变化都不能造成影响，结果是总经办每个部门查得的数据是一致的。

  
  

  风口下的猪2019-06-09【MySQL】

  阅读更多

• 建表后万能操作–Alter

  MySQL

  alter主要是在建表后进行“补充”操作，主要是增删改

  常常和add、modify、change、drop、rename等关键字联用

  一.增加操作add

  1.增加主键列

  alter table tabelname add new_field_id int(5) unsigned default 0 not null auto_increment ,add primary key (new_field_id);

  2.使某列成为主键

  alter table tabelname add primary key (new_field_id);

  3.增加新列

  alter table infos add ex tinyint not null default '0';

  4.增加索引

  alter table tableName add UNIQUE indexName(columnName);【唯一索引】

  alter table tableName add INDEX indexName(columnName);【普通索引】

  alter table tableName add PRIMARY KEY (columnName);【主键索引】

  alter table tableName add FULLTEXT indexName(columnName);【全文索引】

  alter table tableName add INDEX indexName(columnName1,columnName2);【组合索引】

   

  二.删除操作drop

  1.删除主键（主键约束、主键索引）

  alter table primary_test2 drop primary key;

  2.删除外键

  alter table student_tb drop foreign key fk_name;

  3.删除列

  alter table table_name drop column c;

  4.删除字段

  alter table table_name drop c;

  5.删除索引

  alter table tableName drop INDEX indexName;

   

  三.修改操作modify和change以及rename

  （1）修改表信息

  1.修改引擎类型

  alter table tableName engine=myisam;

  2.修改表名

  alter table old_name rename to new_name;

  3.修改表注释

  alter table sys_application comment '系统信息表';

  （2）修改字段信息

  1.修改字段类型和注释

  alter table sys_application modify column app_name varchar(20) COMMENT '应用的名称';

  2.修改字段类型

  alter table sys_application modify column app_name text;

  3.单独修改字段注释

  #目前没发现有单独修改字段注释的命令语句。

  4.设置字段允许为空

  alter table sys_application modify column description varchar(255) null COMMENT '应用描述';

  5.修改为自增主键

  alter table t_app modify column aid int(5) auto_increment ;

  6.修改字段名字(要重新指定该字段的类型)

  alter table t_app change name app_name varchar(20) not null;

  7.调整字段顺序

  alter table t_app change gateway_id gateway_id int not null after aid ; #(注意gateway_id出现了2次)

   

  四.modify和change的区别

  change在使用时，需给定旧列名称和当前列名称及类型。

  （1）改字段名字时

  例如：要把一个integer列的名称从a变更到b：

  alter table t1 change a b integer;

  （2）改字段类型时（新、旧名称一样即可）

  例如：

  alter table t1 change c1 c1 bigint NOT NULL;

  modify来改变列的类型，此时不需要重命名：

  alter table t1 modify c1 bigint NOT NULL;

  风口下的猪2019-06-09【MySQL】

  阅读更多

• MySQL外键约束条件详解

  MySQL

  一.外键的使用条件：

  1.两个表必须是InnoDB表，MyISAM表暂时不支持外键（据说以后的版本有可能支持，但至少目前不持）；

  2.外键列必须建立了索引，MySQL 4.1.2以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显示建立；

  3.外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以，而int和char则不可以；

  二.外键的好处：

  可以使得两张表关联，保证数据的一致性和实现一些级联操作；

  三.外键约束类型：

  语法：on delete 方法  和 on update 方法

  例如：on delete cascade、on update no action

  外键的约束条件主要针对on delete和on update而言。其主要有5个动作：cascade（级联）、set null（为空）、restrict（拒绝）、no action（sql标准的拒绝）、set default（设置默认，目前innodb引擎还不支持）。

  . cascade方式

  在父表上update/delete记录时，同步update/delete掉子表的匹配记录

  注意单独使用on delete cascade时，更新主表数据，会报错不能实现从表对应字段的数据更新。

  同样的，单独使用on update cascade时，删除主表，会报错不能实现从表对应字段的数据删除。

  所以实际项目建表时，两者是一起使用的，即on delete cascade on update cascade

  . set null方式

  在父表上update/delete记录时，将子表上匹配记录的列设为null要注意子表的外键列不能为not null

  . No action方式

  如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作

  . Restrict方式

  同no action, 都是立即检查外键约束

  . Set default方式

  父表有变更时,子表将外键列设置成一个默认的值 但Innodb不能识别

  四.容易出错的地方：

  在使用mysql的外键约束时，很多人都会在创建外键时失败，究其原因，有以下四种：

  1.创建外键时，你的存储引擎应该支持外键并且外表(含有外键的表)和从表(引用外键列的表)的列所使用的存储引擎应该相同；

  2.外表和从表的列必须同时都有相同类型的索引；

  3.外表和从表的列必须同时都有相同的数据类型；

  4.如果表中有数据的话，在创建外键时必须保证外键和所引用的列的值的对应关系。具体来说就是从表的值应该是外表列值的子集(不一定要求真子集)，这也就要求如果在从表中插入新值时，该值必须在外表的外键列中。

  风口下的猪2019-06-09【MySQL】

  阅读更多

• MySQL查看数据表信息，例如约束名称、索引名称

  MySQL

  show create table tableName ;

  风口下的猪2019-06-09【MySQL】

  阅读更多

• MySQL约束

  MySQL

  约束在建表时意义非凡，不可或缺。约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、唯一性。

  一.概念和作用

  约束关键字：constraint

  约束实际上就是表中数据的限制条件

  作用：表在设计的时候加入约束的目的就是为了保证表中的记录完整和有效。

  二.分类

  MySQL中约束有八大指令：非空、唯一、自增、默认、主键、外键、无符号、零填充。

  • 非空约束(not null和null)
  • 唯一性约束(unique)
  • 自增约束（auto_increment）
  • 默认约束（default）
  • 主键约束(primary key) PK
  • 外键约束(foreign key) FK
  • 无符号约束（unsigned）
  • 零填充（zerofill）

   

   

  1.非空约束（not null和null）

  非空约束，不是说只是非空，也可以为空，及非空约束包括两个关键字指令（null和not null）

  null：该字段可以为空；

  not null：该字段不能为空。

  （1）建表时

  新建表时定义支持：列级定义

  列级定义：

  create table temp(

  id int not null,

  name varchar(255) not null default 'abc',

  sex char null

  )；

  （2）建表后

  A.增加非空约束

  alter table user modify sex varchar(2) not null;

  B.取消非空约束

  alter table user modify sex varchar(2) null;

  C.取消非空约束，增加默认值

  alter table user modify sex varchar(2) default 'abc' null;

   

  2.唯一约束（unique）

  唯一约束不允许出现重复的值，但是可以为多个null。

  同一个表可以有多个唯一约束，多个列组合的约束。

  在创建唯一约束时，如果不给唯一约束名称，就默认和列名相同。

  （1）建表时

  新建表支持定义：列级定义+表级定义

  列级定义：

  -- 创建表时设置，表示用户名、密码不能重复

  create table temp(

  id int not null ,

  name varchar(20) unique,

  password varchar(10) unique

  );

  表级定义：

  语法：

  constraint unique_c01 unique(columnName)或者

  unique(columnName)【在创建唯一约束时，如果不给唯一约束名称，就默认和列名相同。】

  unique(columnName1,columnName2)【联合约束，表示两个或以上的字段同时与另一条记录相等，则报错】

  -- 创建表时设置，表示用户名、密码不能重复

  create table temp(

  id int not null ,

  name varchar(20),

  password varchar(10),

  unique(name,password)

  或者

  constraint unique_c01 unique(name,password)

  );

  （2）建表后

  A. 添加唯一约束

  alter table temp add unique (name, password);

  B. 修改唯一约束

  alter table temp modify name varchar(25) unique;

  C. 删除约束

  alter table temp drop index name;

   

  3.自增约束（auto_increment）

  该列数据自增长，与约束一起使用（与主键一起使用）。每个表中只能有一个自增长约束

  （1）建表时

  新建表支持定义：主键列级定义

  create table auto_test(

  id int not null auto_increment primary key,

  name varchar(25)

  );

  （2）建表后

  A.添加自增长约束

  alter table default_test modify id int not null auto_increment

  B.删除自动增长

  alter table auto_test modify id int not null

   

  4.默认约束（default）

  默认值约束 指定某列的默认值

  （1）建表时

  新建表支持定义：列级定义

  create table default_test(

  id int not null auto_increment primary key,

  name varchar(25),

  age int not null default 18

  );

  （2）建表后

  A.添加默认值约束

  alter table default_test modify age int not null default 18

  或者

  alter table default_test change age age int not null default 18

  B.删除默认值约束

  alter table default_test modify age int not null

  或者

  alter table default_test change age age int not null

   

  5.主键约束(primary key) PK

  主键 指定该列的值可以唯一的标识该列记录

  • 主键约束相当于非空约束和唯一约束。
  • 每个表只允许拥有一个主键，但是这个主键可以由多个数据列组成，这些列组合不能重复
  • 标准SQL 允许给主键自行命名，但是对于MySQL来说自己的名字没有任何意义，总是默认为PRIMARY

  简单说：主键这一行的数据不能重复且不能为空。

  （1）建表时

  新建表支持定义：列级定义+表级定义

  列级定义

  create table test_primary(

  id int not null primary key,

  name varchar(50)

  )

  表级定义

  语法：

  constraint PK_c01 primary key(columnName)

  【指定主键约束为 tPK_c01，对大部分数据库有效 但对于MySql无效，此主键约束名仍为PRIMARY】

  primary key(columnName)【在创建主键约束时，如果不给主键约束名称，就默认和列名相同。】

  primary key(columnName1,columnName2)【主键不仅可以是一列，也可以由表中的两列或多列来共同标识，即复合主键】

  create table primary_test3(

  id int not null,

  name varchar(50) not null,

  constraint PK_id_name primary key(id,name)

  或者

  primary key(id,name)

  )

  （2）建表后

  A.有主键时修改主键约束

  alter table primary_test2 modify id int not null primary key

  B.没有主键约束时添加主键约束

  alter table primary_test2 add primary key(id,name)

  C.删除主键约束

  alter table primary_test2 drop primary key

  特别注意：

  # 如果该主键具有自增特性，则在删除主键前需要将自增特性取消，再删除主键

  alter table primary_test2 modify id int not null, drop primary key

   

  6.外键约束(foreign key) FK

  外键 指定该行记录从属主表的一条记录，用于参照完整性

  定义外键的条件：

  • 外键对应的字段数据类型保持一致,且被关联的字段(即references指定的另外一个表的字段)，必须保证唯一
  • 所有tables的存储引擎必须是InnoDB类型.
  • 外键的约束4种类型: 1.RESTRICT 2. NO ACTION 3.CASCADE 4.SET NULL

  建议

  • 如果需要外键约束,最好创建表同时创建外键约束.
  • 如果需要设置级联关系,删除时最好设置为 SET NULL.

  （1）建表时

  新建表支持定义：表级定义

  语法：

  foreign key (B_columnName1) references A_tableName(A_columnName1)

  ON DELETE CASCADE

  ON UPDATE CASCADE

  或者

  constraint FK_Name foreign key(B_columnName1) references A_tableName(A_columnName1)

  ON DELETE CASCADE

  ON UPDATE CASCADE

  create table teacher_tb(

  name varchar(255),

  pass varchar(255),

  primary key(name,pass)

  );

  create table student_tb(

  id int auto_increment not null primary key,

  name varchar(50) not null,

  py_pass varchar(255),

  py_name varchar(255),

  constraint fk_stu foreign key(py_pass,py_name) references teacher_tb(name,pass)

  )

  #支持建立多列组合外键约束

  （2）建表后

  A.删除外键约束

  alter table student_tb drop foreign key fk_stu

  B.添加外键约束

  alter table student_tb add foreign key (py_pass,py_name) references teacher_tb(name,pass)

  on delete cascade

  on update cascade

   

  7.无符号约束（unsigned）

  无符号约束仅作用于数值类型

  （1）建表时

  建表支持定义：列级定义

  CREATE TABLE t_user(

  user_id INT(10) UNSIGNED

  );

  （2）建表后

  A.增加无符号约束

  ALTER TABLE t_user MODIFY user_id INT(10) UNSIGNED;

  或者

  ALTER TABLE t_user CHANGE user_id user_id INT(10) UNSIGNED;

  B.删除无符号约束

  ALTER TABLE t_user MODIFY user_id INT(10);

  或者

  ALTER TABLE t_user CHANGE user_id user_id INT(10);

   

  8.零填充约束（zerofill）

  （1）建表时

  新建表定义支持：列级定义

  create table zero_test(

  id int not null auto_increment primary key,

  age int zerofill

  );

  （2）建表后

  A.天机零填充约束

  alter table zer_test modify age int zerofill

  或者

  alter table zer_test change age age int zerofill

  B.删除零填充约束

  alter table zero_test modify age int

  或者

  alter table zero_test change age age int

  风口下的猪2019-06-09【MySQL】

  阅读更多