网站怎么做后台,谷德设计网站,wordpress require_once,好一点的网站前言对于后端开发同学来说#xff0c;访问数据库#xff0c;是代码中必不可少的一个环节。系统中收集到用户的核心数据#xff0c;为了安全性#xff0c;我们一般会存储到数据库#xff0c;比如#xff1a;mysql#xff0c;oracle等。后端开发的日常工作#xff0c;需要…前言对于后端开发同学来说访问数据库是代码中必不可少的一个环节。系统中收集到用户的核心数据为了安全性我们一般会存储到数据库比如mysqloracle等。后端开发的日常工作需要不断的建库和建表来满足业务需求。通常情况下建库的频率比建表要低很多所以我们这篇文章主要讨论建表相关的内容。如果我们在建表的时候不注意细节等后面系统上线之后表的维护成本变得非常高而且很容易踩坑。今天就跟大家一起聊聊数据库建表的15个小技巧希望对你会有所帮助。1.名字建表的时候给表、字段和索引起个好名字真的太重要了。1.1 见名知意名字就像表、字段和索引的一张脸可以给人留下第一印象。好的名字言简意赅见名知意让人心情愉悦能够提高沟通和维护成本。坏的名字模拟两可不知所云。而且显得杂乱无章看得让人抓狂。反例用户名称字段定义成yong_hu_ming、用户_name、name、user_name_123456789你看了可能会一脸懵逼这是什么骚操作正例用户名称字段定义成user_name温馨提醒一下名字也不宜过长尽量控制在30个字符以内。1.2 大小写名字尽量都用小写字母因为从视觉上小写字母更容易让人读懂。反例字段名PRODUCT_NAME、PRODUCT_name全部大写看起来有点不太直观。而一部分大写一部分小写让人看着更不爽。正例字段名product_name名字还是使用全小写字母看着更舒服。1.3 分隔符很多时候名字为了让人好理解有可能会包含多个单词。那么多个单词间的分隔符该用什么呢反例字段名productname、productName、product name、productname单词间没有分隔或者单词间用驼峰标识或者单词间用空格分隔或者单词间用分隔这几种方式都不太建议。正例字段名product_name强烈建议大家在单词间用_分隔。1.4 表名对于表名在言简意赅见名知意的基础之上建议带上业务前缀。如果是订单相关的业务表可以在表名前面加个前缀order_。例如order_pay、order_pay_detail等。如果是商品相关的业务表可以在表名前面加个前缀product_。例如product_spuproduct_sku等。这样做的好处是为了方便归类把相同业务的表可以非常快速的聚集到一起。另外还有有个好处是如果哪天有非订单的业务比如金融业务也需要建一个名字叫做pay的表可以取名finance_pay就能非常轻松的区分。这样就不会出现同名表的情况。1.5 字段名称字段名称是开发人员发挥空间最大但也最容易发生混乱的地方。比如有些表使用flag表示状态另外的表用status表示状态。可以统一一下使用status表示状态。如果一个表使用了另一个表的主键可以在另一张表的名后面加_id或_sys_no例如在product_sku表中有个字段是product_spu表的主键这时候可以取名product_spu_id或product_spu_sys_no。还有创建时间可以统一成create_time修改时间统一成update_time。删除状态固定为delete_status。其实还有很多公共字段在不同的表之间可以使用全局统一的命名规则定义成相同的名称以便于大家好理解。1.6 索引名在数据库中索引有很多种包括主键、普通索引、唯一索引、联合索引等。每张表的主键只有一个一般使用id或者sys_no命名。普通索引和联合索引其实是一类。在建立该类索引时可以加ix_前缀比如ix_product_status。唯一索引可以加ux_前缀比如ux_product_code。2.字段类型在设计表时我们在选择字段类型时可发挥空间很大。时间格式的数据有date、datetime和timestamp等等可以选择。字符类型的数据有varchar、char、text等可以选择。数字类型的数据有int、bigint、smallint、tinyint等可以选择。说实话选择很多有时候是一件好事也可能是一件坏事。如何选择一个合适的字段类型变成了我们不得不面对的问题。如果字段类型选大了比如原本只有1-10之间的10个数字结果选了bigint它占8个字节。其实1-10之间的10个数字每个数字1个字节就能保存选择tinyint更为合适。这样会白白浪费7个字节的空间。如果字段类型择小了比如一个18位的id字段选择了int类型最终数据会保存失败。所以选择一个合适的字段类型还是非常重要的一件事情。以下原则可以参考一下尽可能选择占用存储空间小的字段类型在满足正常业务需求的情况下从小到大往上选。如果字符串长度固定或者差别不大可以选择char类型。如果字符串长度差别较大可以选择varchar类型。是否字段可以选择bit类型。枚举字段可以选择tinyint类型。主键字段可以选择bigint类型。金额字段可以选择decimal类型。时间字段可以选择timestamp或datetime类型。3.字段长度前面我们已经定义好了字段名称选择了合适的字段类型接下来需要重点关注的是字段长度了。比如varchar(20)biginit(20)等。那么问题来了varchar代表的是字节长度还是字符长度呢答在mysql中除了varchar和char是代表字符长度之外其余的类型都是代表字节长度。biginit(n) 这个n表示什么意思呢假如我们定义的字段类型和长度是bigint(4)bigint实际长度是8个字节。现在有个数据a1a显示4个字节所以在不满4个字节时前面填充0前提是该字段设置了zerofill属性比如0001。当满了4个字节时比如现在数据是a123456它会按照实际的长度显示比如123456。但需要注意的是有些mysql客户端即使满了4个字节也可能只显示4个字节的内容比如会显示成1234。所以bigint(4)这里的4表示显示的长度为4个字节实际长度还是占8个字节。4.字段个数我们在建表的时候一定要对字段个数做一些限制。我之前见过有人创建的表有几十个甚至上百个字段表中保存的数据非常大查询效率很低。如果真有这种情况可以将一张大表拆成多张小表这几张表的主键相同。建议每表的字段个数不要超过20个。5. 主键在创建表时一定要创建主键。因为主键自带了主键索引相比于其他索引主键索引的查询效率最高因为它不需要回表。此外主键还是天然的唯一索引可以根据它来判重。在单个数据库中主键可以通过AUTO_INCREMENT设置成自动增长的。但在分布式数据库中特别是做了分库分表的业务库中主键最好由外部算法(比如雪花算法生成它能够保证生成的id是全局唯一的。除此之外主键建议保存跟业务无关的值减少业务耦合性方便今后的扩展。不过我也见过有些一对一的表关系比如用户表和用户扩展表在保存数据时是一对一的关系。这样用户扩展表的主键可以直接保存用户表的主键。6.存储引擎在mysql5.1以前的版本默认的存储引擎是myslam而mysql5.1以后的版本默认的存储引擎变成了innodb。之前我们还在创建表时还一直纠结要选哪种存储引擎myslam的索引和数据分开存储而有利于查询但它不支持事务和外键等功能。而innodb虽说查询性能稍微弱一点但它支持事务和外键等功能更强大一些。以前的建议是读多写少的表用myslam存储引擎。而写多读多的表用innodb。但虽说mysql对innodb存储引擎性能的不断优化现在myslam和innodb查询性能相差已经越来越小。所以建议我们在使用mysql8以后的版本时直接使用默认的innodb存储引擎即可无需额外修改存储引擎。7. NOT NULL在创建字段时需要选择该字段是否允许为NULL。我们在定义字段时应该尽可能明确该字段NOT NULL。为什么呢我们主要以innodb存储引擎为例myslam存储引擎没啥好说的。主要有以下原因在innodb中需要额外的空间存储null值需要占用更多的空间。null值可能会导致索引失效。null值只能用is null或者is not null判断用号判断永远返回false。因此建议我们在定义字段时能定义成NOT NULL就定义成NOT NULL。但如果某个字段直接定义成NOT NULL万一有些地方忘了给该字段写值就会insert不了数据。这也算合理的情况。但有一种情况是系统有新功能上线新增了字段。上线时一般会先执行sql脚本再部署代码。由于老代码中不会给新字段赋值则insert数据时也会报错。由此非常有必要给NOT NULL的字段设置默认值特别是后面新增的字段。例如alter table product_sku add column brand_id int(10) not null default 0;8.外键在mysql中是存在外键的。外键存在的主要作用是保证数据的一致性和完整性。例如create table class (id int(10) primary key auto_increment,cname varchar(15)
);有个班级表class。然后有个student表create table student(id int(10) primary key auto_increment,name varchar(15) not null,gender varchar(10) not null,cid int,foreign key(cid) references class(id)
);其中student表中的cid字段保存的class表的id这时通过foreign key增加了一个外键。这时如果你直接通过student表的id删除数据会报异常a foreign key constraint fails必须要先删除class表对于的cid那条数据再删除student表的数据才行这样能够保证数据的一致性和完整性。顺便说一句只有存储引擎是innodb时才能使用外键。如果只有两张表的关联还好但如果有十几张表都建了外键关联每删除一次主表都需要同步删除十几张子表很显然性能会非常差。因此互联网系统中一般建议不使用外键。因为这类系统更多的是为了性能考虑宁可牺牲一点数据一致性和完整性。除了外键之外存储过程和触发器也不太建议使用他们都会影响性能。9. 索引在建表时除了指定主键索引之外还需要创建一些普通索引。例如create table product_sku(id int(10) primary key auto_increment,spu_id int(10) not null,brand_id int(10) not null,name varchar(15) not null
);在创建商品表时使用spu_id商品组表和brand_id品牌表的id。像这类保存其他表id的情况可以增加普通索引create table product_sku (id int(10) primary key auto_increment,spu_id int(10) not null,brand_id int(10) not null,name varchar(15) not null,KEY ix_spu_id (spu_id) USING BTREE,KEY ix_brand_id (brand_id) USING BTREE
);后面查表的时候效率更高。但索引字段也不能建的太多可能会影响保存数据的效率因为索引需要额外的存储空间。建议单表的索引个数不要超过5个。如果在建表时发现索引个数超过5个了可以删除部分普通索引改成联合索引。顺便说一句在创建联合索引的时候需要使用注意最左匹配原则不然建的联合索引效率可能不高。对于数据重复率非常高的字段比如状态不建议单独创建普通索引。因为即使加了索引如果mysql发现全表扫描效率更高可能会导致索引失效。10.时间字段时间字段的类型我们可以选择的范围还是比较多的目前mysql支持date、datetime、timestamp、varchar等。varchar类型可能是为了跟接口保持一致接口中的时间类型是String。但如果哪天我们要通过时间范围查询数据效率会非常低因为这种情况没法走索引。date类型主要是为了保存日期比如2020-08-20不适合保存日期和时间比如2020-08-20 12:12:20。而datetime和timestamp类型更适合我们保存日期和时间。但它们有略微区别。timestamp用4个字节来保存数据它的取值范围为1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07。此外它还跟时区有关。datetime用8个字节来保存数据它的取值范围为1000-01-01 00:00:00 ~ 9999-12-31 23:59:59。它跟时区无关。优先推荐使用datetime类型保存日期和时间可以保存的时间范围更大一些。温馨提醒一下在给时间字段设置默认值是建议不要设置成0000-00-00 00:00:00不然查询表时可能会因为转换不了而直接报错。11.金额字段mysql中有多个字段可以表示浮点数float、double、decimal等。而float和double可能会丢失精度因此推荐大家使用decimal类型保存金额。一般我们是这样定义浮点数的decimal(m,n)。其中n是指小数的长度而m是指整数加小数的总长度。假如我们定义的金额类型是这样的decimal(10,2)则表示整数长度是8位并且保留2位小数。12.唯一索引唯一索引在我们实际工作中使用频率相当高。你可以给单个字段加唯一索引比如组织机构code。也可以给多个字段加一个联合的唯一索引比如分类编号、单位、规格等。单个的唯一索引还好但如果是联合的唯一索引字段值出现null时则唯一性约束可能会失效。创建唯一索引时相关字段一定不能包含null值否则唯一性会失效。13.字符集mysql中支持的字符集有很多常用的有latin1、utf-8、utf8mb4、GBK等。这4种字符集情况如下latin1容易出现乱码问题在实际项目中使用比较少。而GBK支持中文但不支持国际通用字符在实际项目中使用也不多。从目前来看mysql的字符集使用最多的还是utf-8和utf8mb4。其中utf-8占用3个字节比utf8mb4的4个字节占用更小的存储空间。但utf-8有个问题即无法存储emoji表情因为emoji表情一般需要4个字节。由此使用utf-8字符集保存emoji表情时数据库会直接报错。所以建议在建表时字符集设置成utf8mb4会省去很多不必要的麻烦。14. 排序规则不知道你关注过没在mysql中创建表时有个COLLATE参数可以设置。例如CREATE TABLE order (id bigint NOT NULL AUTO_INCREMENT,code varchar(20) COLLATE utf8mb4_bin NOT NULL,name varchar(30) COLLATE utf8mb4_bin NOT NULL,PRIMARY KEY (id),UNIQUE KEY un_code (code),KEY un_code_name (code,name) USING BTREE,KEY idx_name (name)
) ENGINEInnoDB AUTO_INCREMENT5 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_bin它是用来设置排序规则的。字符排序规则跟字符集有关比如字符集如果是utf8mb4则字符排序规则也是以utf8mb4_开头的常用的有utf8mb4_general_ci、utf8mb4_bin等。其中utf8mb4_general_ci排序规则对字母的大小写不敏感。说得更直白一点就是不区分大小写。而utf8mb4_bin排序规则对字符大小写敏感也就是区分大小写。说实话这一点还是非常重要的。假如order表中现在有一条记录name的值是大写的YOYO但我们用小写的yoyo去查例如select * from order where nameyoyo;如果字符排序规则是utf8mb4_general_ci则可以查出大写的YOYO的那条数据。如果字符排序规则是utf8mb4_bin则查不出来。由此字符排序规则一定要根据实际的业务场景选择否则容易出现问题。15.大字段我们在创建表时对一些特殊字段要额外关注比如大字段即占用较多存储空间的字段。比如用户的评论这就属于一个大字段但这个字段可长可短。但一般会对评论的总长度做限制比如最多允许输入500个字符。如果直接定义成text类型可能会浪费存储空间所以建议将这类字段定义成varchar类型的存储效率更高。当然我还见过更大的字段即该字段直接保存合同数据。一个合同可能会占几Mb。在mysql中保存这种数据从系统设计的角度来说本身就不太合理。像合同这种非常大的数据可以保存到mongodb中然后在mysql的业务表中保存mongodb表的id。最后说一句(求关注别白嫖我)如果这篇文章对您有所帮助或者有所启发的话帮忙扫描下发二维码关注一下您的支持是我坚持写作最大的动力。求一键三连点赞、转发、在看。
