创建主键等常用操作总结,设计表的时候

2019-11-02 01:12 来源:未知

 

一. 列常用操作

① 添加新的一列test_column,并将其作为主键,FIRST将其放在表中第一行,auto_increement是自动增长

alter table test_table add column test_column int not null auto_increment FIRST add primary key(test_column);
  • 1

 

可以使用SQL语句“alter table ai3 add id0 int  auto_increment primary key first;”来添加主键列。可以使用SQL语句“alter table ai4 modify id int auto_increment primary key;”来修改主键列。

② 删除列

 

alter table test_table drop column test_column;
  • 1

③ 修改某一列的字段长度(例如本来是30字节改为50字节长)

alter table test_table modify column test_column varchar(50);
  • 1

④ 完全修改某一列(假设原本列名是test1_column,类型是int)

alter table test_table change column test1_column test_column varchar(30);
  • 1

⑤ 仅仅想重命名某一列(首先需要了解这一列的类型,假如原本是int且不为空,列名是error_name_column)

alter table test_table change column error_name_column test_column int not null;
  • 1

不管是在MSSQL还是MySQL或者Oracle,变长字段的长度衡量都是要经常面对的。
对于一个变长的字段,在满足业务的情况下(其实所谓的满足业务是一个比较模糊的东西),到底是选择varchar(50)还是varchar(200)亦或是varchar(500)?
对于保守型选择,往往是选择一个较大的长度,比如varchar(500)要比varchar(50)更具有兼容性,因为是变长字段的原因,存储空间也一样。
这样的选择并不能说就不好,看站在哪个角度来看问题。
那么,相对于varchar(50),varchar(500)在更具备兼容性的同时,有哪些不好的地方,也是需要思考的,。

Preface

二. 针对表的多数操作

① 修改指定表的存储引擎,假设原本是MYISAM

alter table test_table engine=innodb;
  • 1

② 删除指定表的主键

alter table test_table drop primary key;
  • 1

这里有个情况需要指出,如果该主键列是自动增长(auto_increment)的,因为mysql要求自动增长列必须是索引,所以删除主键也就删除了主键索引,这是不符合mysql要求的,是无法实现的,会报错,必须先删除自动增长(通过修改列属性),后删除主键

③ 为指定表添加主键

alter table test_table add primary key(test_column);
  • 1

④ 为指定表添加索引(普通索引),test_index是索引名

alter table test_table add index test_index(test_column);
  • 1

⑤ 删除指定表索引

alter table test_table drop index test_index;
  • 1

⑥ 重命名表

alter table test_table rename new_name_table;

 

 



 

 

如果想在一个已经建好的表中添加一列,可以用诸如:

alter table TABLE_NAME add column NEW_COLUMN_NAME varchar(20) not null;

这条语句会向已有的表中加入新的一列,这一列在表的最后一列位置。如果我们希望添加在指定的一列,可以用:

alter table TABLE_NAME add column NEW_COLUMN_NAME varchar(20) not null after COLUMN_NAME;

注意,上面这个命令的意思是说添加新列到某一列后面。如果想添加到第一列的话,可以用:

alter table TABLE_NAME add column NEW_COLUMN_NAME varchar(20) not null first;

这里的原则就是:对于可变长度的字段,在满足条件的前提下,尽可能使用较短的变长字段长度。

 

 

    Today,I'm gonna implement a PXC,Let's see the procedure.

以下是一个相对极端的例子,以SQL Server为例,
TestVarchar1和TestVarchar2的SortColumn 字段长度分别是varchar(50)和varchar(8000),两个表写入10000条测一样的试数据,
SortColumn 的实际长度是36个字符。

 

Create Table TestVarchar1
(
    Id INT IDENTITY(1,1),
    SortColumn varchar(50)
)

Create Table TestVarchar2
(
    Id INT IDENTITY(1,1),
    SortColumn varchar(8000)
)

DECLARE @SortColumn char(36);
set @SortColumn = CAST(NEWID() as char(36))
insert into TestVarchar1(SortColumn) values (@SortColumn)
insert into TestVarchar2(SortColumn) values (@SortColumn)
GO 10000

Framework

 

 

1,基于存储空间的考虑

Hostname IP Port OS Version MySQL Version Xtrabackup version
zlm2 192.168.1.101 3308 CentOS 7.0 PXC 5.7.22 2.4.12 Linux (x86_64)
zlm3 192.168.1.102 3308 CentOS 7.0 PXC 5.7.22 2.4.12 Linux (x86_64)
zlm4 192.168.1.103 3308 CentOS 7.0 PXC 5.7.22 2.4.12 Linux (x86_64)

存储空间上,存储不超过一定长度的变长字段,不同长度的变长字段存储空间是一样的,比如选择使用varchar(50)和varchar(500)是一样的,
也就说,对于不超过50个字符串的数据存储,两者在物理空间占用上并没有区别。

 

这里会发现,两个表的数据在完全一致的情况下,其存储空间也是完全一样的,的确,并不会因为varchar使用一个较长的长度而多占用存储空间

Procedure

图片 1

 

2,基于性能的考虑
选择varchar(50)还是varchar(8000),在性能上确实有显著的差异,考虑到某些查询需要内存(Memory Grant),查询引擎会预估当前查询需要的内存,影响查询内存的因素有以下几个方面
1,查询的类型,有没有聚合运算,有没有排序等等
2,每个操作符涉及到的记录数量
3,数据行的大小(这里是字段类型的长度而不是字段实际长度)
当行记录的数据类型长度较大的时候,执行计划预估的平均大小较大,数据类型定义的长度越大,预估的长度越大,需要分配的内存越大
如果一个查询涉及一些聚合操作并且数据量较大,就可能需要大量的内存来完成这个查询,查询引起会分配多余实际需要的内存。

TAG标签:
版权声明:本文由澳门金莎娱乐网站发布于数据库,转载请注明出处:创建主键等常用操作总结,设计表的时候