mysql alter对表的操作

电玩女神 2022-05-31 00:27 210阅读 0赞

之前书上对更改table的介绍很少，今天我专门去小小研究了一波

*  alter 修改表
    
     *  表更名
     *  修改表的主键
        
         *  添加主键
         *  移除原有的主键
         *  主键id自增
     *  修改column表属性
        
         *  添加属性
         *  删除属性
         *  移动属性位置
         *  更改属性名称
         *  mysql 复制replication 的时候对自增字段的处理

# alter 修改表 #

## 表更名 ##

简述：当你建表的时候，你发现我的表的名字打错了。我要重命名表。

create table emplyee( id int );
    -- 发现表的名字写错了，修改表名
    alter table emplyee rename employee;

## 修改表的主键 ##

### 添加主键 ###

简述：用add 来给原来的表增加主键

alter table employee add primary key(id);

### 移除原有的主键： ###

简述：drop 移除原有的主键

alter table employee drop primary key;

### 主键id自增 ###

简述：modify 修改原来的主键，附上原有的约束，not null，增加自增，auto\_increment，int型是可以使用自增的。

alter table employee modify id int not null auto_increment;

![这里写图片描述][SouthEast]

*  注意：在添加自增之后，当我们drop想要移除这个主键的时候，我们需要先移除自增。不然会有报错信息：

> Error Code: 1075. Incorrect table definition; there can be only one auto column and it must be defined as a key

-- 移除很简单，就是modify修改原属性就行
    alter table employee modify id int not null ;

*  关于自增的知识补充下：（摘录的）  
    AUTO\_INCREMENT说明：

（1）如果把一个NULL插入到一个AUTO\_INCREMENT数据列里去，MySQL将自动生成下一个序列编号。编号从1开始，并1为基数递增。

（2）把0插入AUTO\_INCREMENT数据列的效果与插入NULL值一样。但不建议这样做，还是以插入NULL值为好。

（3）当插入记录时，没有为AUTO\_INCREMENT明确指定值，则等同插入NULL值。  
（这句话不一定对，最好还是自增的时候写上null，因为主键那一列插入数据的时候它不能为空啊，会显示value的值个数不匹配）

（4）当插入记录时，如果为AUTO\_INCREMENT数据列明确指定了一个数值，则会出现两种情况，情况一，如果插入的值与已有的编号重复，则会出现出错信息，因为AUTO\_INCREMENT数据列的值必须是唯一的；情况二，如果插入的值大于已编号的值，则会把该插入到数据列中，并使在下一个编号将从这个新值开始递增。也就是说，可以跳过一些编号。

（5）如果用UPDATE命令更新自增列，如果列值与已有的值重复，则会出错。如果大于已有值，则下一个编号从该值开始递增。

**几点注意**：

*  对于innodb表，自动增长列必须是索引，且必须是组合索引的第一列，且一个表只能有一个auto\_increment属性。
 *  可以通过alter table t1 auto\_incremenrt=n 语句强制设置自动增长列的初始值，默认从1开始，但是该强制的默认值是保留在内存中的，如果该值在使用之前数据库重新启动，那么这个强制的默认值就会丢失，就需要数据库启动后重新设置
 *  可以是用last\_insert\_id()查询当前线程最后插入记录使用的值。如果一次插入多条记录，那么返回的是第一条记录使用的自动增长值。
 *  如果用delete删除了号6，那么再次插入的时候不会用6了，而是直接用7，会跳号，这样也挺好的，免得回到之前的号继续插，引起重复。如果能自动检测未使用的号，就好了～

## 修改column表属性 ##

### 添加属性 ###

简述：在原有的属性上添加新的属性，并实现约束条件

alter table employee add column city varchar(50)not null;
    alter table employee add column address varchar(50)not null;

### 删除属性 ###

简述：删除表中不要的属性

alter table employee drop city

即使有数据，也会一次性删除，我觉得这个地方应该增加一个确认键更好些，万一是误删呢？不过既然用命令删了，不存在误删了把？

### 移动属性位置 ###

-- after xxx 移动到xxx之后，移动的时候记得带上原有的约束 
    alter table employee modify address varchar(50) not null after city;
    -- first 把某一个column 移动到第一位，其余的column往后顺延
    alter table employee modify address varchar(50) not null first;

### 更改属性名称 ###

change 更改列表名,modify 和 change的区别估计就在更改名字上面把

alter table employee change `address` `address1` varchar(50) not null;

### mysql 复制replication 的时候对自增字段的处理 ###

备用知识：（摘录的）

*  mysql中有自增长字段，在做数据库的主主同步时需要在参数文件中设置自增长的两个相关配置：auto\_increment\_offset和auto\_increment\_increment。
 *  auto\_increment\_offset表示自增长字段从那个数开始，他的取值范围是1 .. 65535
 *  auto\_increment\_increment表示自增长字段每次递增的量，其默认值是1，取值范围是1 .. 65535

对于replication（复制）的master-master方式 为防止auto\_increment字段的重复，可做如下设置

A服务器的my.cnf设置如下：

auto_increment_offset = 1
    auto_increment_increment = 2

这样A的auto\_increment字段产生的数值是：1, 3, 5, 7, …

B服务器的my.cnf设置如下：

auto_increment_offset = 2
    auto_increment_increment = 2

这样B的auto\_increment字段产生的数值是：2, 4, 6, 8, …

根据官方的说明：If the value of auto\_increment\_offset is greater than that of auto\_increment\_increment, the value of auto\_increment\_offset is ignored. （如果auto\_increment\_offset的值大于auto\_increment\_increment的值，则auto\_increment\_offset的值会被忽略）

[SouthEast]: /images/20220531/692b61dcc03a4c5a9a5fbce4b90851ac.png