mysql数据库教程外联_活字格外联数据库SQLServer和Mysql
的经验（⼤多数经验。。。
来⾃学习和实操后的总结，有说得不对的，或者遗漏的，⼤家留⾔补充。希望这个贴⼦，能成为活字格⽼铁们使⽤外联库的⼀个指南。PS 即使你不打算使⽤外联库，⾥⾯的⼀些⽅法，也值得看⼀看！
⼀、库表规划
1、系统表：如组织架构、⽤户、⾓⾊、权限等。活字格内置在sqlite中，⽆法直连，可通过视图⽅式读取出来
2、词典表dictionary：辅助填报的列表信息，如⾏政区域、男⼥、学历、岗位、合同类型、商品类别等。其中数据量多、结构⽐较独特或需要中台设定的，单独做表，如⾏政区域表(记录多)、商品类别(中台设定)等，其它放在通⽤词典表，做⼀些表设计，我⼀般是设置类别字段、属性字段(固定参/可变参)、排序字段、图⽚字段、三个字符字段，三个⼩数字段。
3、业务表business：记录业务的发⽣，这是使⽤最多的表
4、当前表current：存放当前值/累计值或档案类，如当前库存、累计销售、商品档案等
5、期末表end：存放某个时期的期末结存数，如⽉末库存
6、期间表period：存放某个时期内的发⽣值，如⽉销售统计
⼆、建表
1、表命名：⽬前我使⽤的表命令⽅法：模块英⽂标识_表主名使⽤驼峰法，如hr_employee，hr_employeeFamily
2、视图命名：模块英⽂标识v_视图的作⽤
3、函数命名：模块英⽂标识p_函数的作⽤
4、表名/字段长度：虽然mssql可以128个字符，但还是建议在64个以内
5、字段名全部⽤英⽂，字段全部⽤⼩写英⽂字母，字母开头，不同单词⽤_连接，不⽤复词，即使是内置的sqlite，也建议⽤英⽂名
6、表/字段设计时，尽可能都加注释，我会把字段注释全部⼊到表注释那⾥，省时间。如果字段有固定参的，最好也把固定参定上(2020-9-5改)
7、临时表，使⽤内置的sqlite表，可以提升效率，特别是进⾏中间计算或笛卡尔积时，能极⼤的提升效率，注意，是极⼤！
8、在活字格内先建表，然后在数据库IDE中设计表。这样会在前⾯⼏个字段⾃动建⽴ID(默认主键)、FGC_CreateDate、
FGC_LastModifier、FGC_LastModifyDate、FGC_Creator、FGC_Rowversion、FGC_UpdateHelp字段。主键、并发、权限问题⼀次性解决。PS.⾃建字段千万不要去动，另外，外联表的字段设计不要在活字格⾥弄，因为数据类型有限。
三、字段
1、mysql⾥能⽤char，就不⽤vchar，mssql也⼀样。另外，mssql⾥，如果有中⽂就⽤nchar和nvarchar，纯英⽂和数字，那就⽤char 和varchar
2、vchar不超过5000，如果超过，⽤text，并独⽴出⼀张表，⽤主键对应
3、tinyint-255，smallint-65535，int-42.9亿，bigint-最⼤，实际使⽤时再放⼤⼏个零
4、⼩数类型，如果是⾦融类的⼀定要⽤decimal(18，2)，float(7位)和double(15位)⾃⼰选择。(2020-9-5改)
5、是否字段⽤is_，如果是表现已经状态，如is_deleted，1表⽰已删除
6、数据表虽然有⼆进制字段，但活字格的图⽚和附件都是以字符串形式记录地址，以防万⼀，在mssql⾥我都直接⽤nvarchar(max)来
7、表名、字段名、字段类型真得⾮常重要，在⽣产环境中，基本上没有机会可以更改，对有长度的字段类型，还是要留有余量
8、⼀张表只能有⼀个timestamp时间戳类型，如果你给活字格修改数据表的权限，活字格会⾃动帮你建⼀个，那是⽤来防⽌并发脏读的。重复⼀次，活字格⾃动建的⼏个字段，不要去改动，包括字段类型也不要去动！
9、SQL数据库的⽇期，⼀般有date、datetime、datetime2三种，活字格的⽇期对应的是datetime。之前，为了省资源，将⽇期设为了date，但期间出现的⼏次时间字段的数据库报错。出现⼏次后，发现了规律，就是数据库连接断开，重新连的时候，活字格会把date字段识别为⽂本，这样就会出现报错。所以，建议外联数据库的⽇期还是设置为datetime，时间则设置为time(7,0)，和活字格的机制保持⼀致！
四、主键/外键
1、主键最好只⽤⾃然主键，不要和业务关联，⽐如活字格⾃动新建的ID字段，同时尽量避免⽤联合主键
2、外键使⽤⽐较讲究，先说逻辑外键，就是活字格中设置关联字段，但不设置联级约束，就是逻辑外键(在活字格⾥不能设置外联表的联级约束，需要在数据库IDE中设置)，逻辑外键可以随意⽤。但联级约束外键要慎⽤，因为每次增改删都会触发联级查询，会影响效率。阿⾥的开发⼿册⼲脆就直接⼲掉联级外键了。
3、如果⼀定要⽤联级外键，注意以下⼏点：(1)引⽤列并不⼀定⾮要主键，但必须是唯⼀列；(2)外键列与引⽤列的数据类型/字符集/校对规则要⼀致；(3)外键列和引⽤列都必须建索引；(4)外键列引⽤多个列的，列顺序要⼀致；(5)⼤对象字段不要⽤做引⽤列；(6)外键命名在库⾥要唯⼀，命名规则fk_本表名_引⽤表名_on_引⽤字段，表名的模块缩写和结尾标识去掉；(6)如果⼦表弄了触发器，外键联级更新是不会触发的，所以这种情况不要⽤；(7)不再⽀持分区表了，所以有计划⽤分区表的，不要⽤外键；(8)⾼并发更新表情况下，⼀定⼀定要避免使⽤联级外键
4、虽然阿⾥不建议⽤联级外键，但在⾮常严格要求主⼦表⼀致性、表结构简单、并发不⾼、查询数据量不⾼时，可以⽤⼀下。其它情况，需要联级约束的，最好在前端通过设计来实现
5、⾝份证号、银⾏账号、社保账号之类敏感的字段，不要做主键或外键
五、索引
1、索引命名：主键索引pk_表名_字段名，表名的模块缩写和结尾标识去掉，唯⼀索引uk_，普通索引idx_。如果你选择在活字格中建外联表，会⾃动建⽴⼀个ID的索引(主键⾃动建索引)，最好按规则改⼀下名。注：索引需要在数据表IDE中建，活字格中建不了
2、业务上具有唯⼀特性的字段，即使是多个字段的组合，如果要建⽴索引，则必须建成唯⼀索引
3、索引如果不确定要⽤，可以先不设置，后期进⾏sql优化时再使⽤。⼀般建在多表联接⽤的关联字段以及查询频率较⾼的条件字段上。
4、vchar索引，如果⽐较长，需要注意。⼀般20长度已经能覆盖90%的查询判断， 可以⽤count(distinct left(列名, 索引长度))和
count(*)的来测试决定，设置⽅式column_name(length)
5、where和orderby有索引字段，且该字段在复合索引中时，将该字段放在复合索引最后
6、建组合索引的时候，区分度最⾼的在最左边。如where a=? and b=? ，如果 a 列⼏乎接近于唯⼀值，那么只需要单建a列 索引即可
7、存在⾮等号和等号混合时，在建索引时，请把等号条件的列前置。如：where c>? and d=? 那么即使 c 的区分度更⾼，也必须把 d 放在索引的最前列，即索引 idx_d_c
8、不要索引常⽤的⼩型表
9、查看select是否使⽤了索引、使⽤了哪个索引，可以使⽤⽤执⾏计划(EXPLAIN，在IDE中使⽤)查询结果，看key和key_len两项
10、mssql设置为主键和唯⼀键的，会⾃动建索引，不需要另外建；mysql的唯⼀键，是通过建⽴唯⼀性的索引来设置，主键也会⾃动建索引；
六、其它
1、join不要超过3个表，join字段数据类型必须⼀致。多表关联查询时，保证被关联的字段需要有索引。
2、查询先快速定位需要获取的 id 段，然后再关联，如：SELECT a.* FROM 表 1 a, (select id from 表 1 where 条件 LIMIT
100000,20 ) b where a.id=b.id
3、如果对字符编码和引擎不熟悉，反正看到UTF8、innodb的字眼，选它就对
4、反范式：字段允许适当冗余，以提⾼查询性能，但必须考虑数据⼀致。冗余字段应遵循：(1)不是频繁修改的字段；(2)不是 varchar 超长字段，更不能是 text 字段；(3)不是唯⼀索引的字段。举个例⼦就明⽩：商品类⽬名称使⽤频率⾼，字段长度短，名称基本⼀不变，可在相关联的表中冗余存储类⽬名称，避免关联查询。看了⼀个测试，150万的数据，⼀个35秒，⼀个⼏秒，惊呆了
5、通常数据库优化的⽅向：(1)服务器硬件(三⼤件)；(2)服务器布置环境(⽐如你⽤mysql，就⼀定不能在windows环境⾥)；(3)SQL本⾝优化；(4)反范式；(5)索引优化。查⼀下关键词“慢查询”、“慢sql”、“perl”。
6、阿⾥开发⼿册是禁⽌使⽤存储过程的，认为存储过程难以调试和扩展，更没有移植性。但也有⼈认为可以使⽤，⽐如⼀些常⽤的关联表功能组，还是可以封装。但原则是前后端设计能解决的，就不要⽤存储过程，特别是现在有了服务端命令，应该可以尽量避免使⽤
7、设计视图要从业务出发，视图和业务建⽴⼀对⼀的关系，不要因为想让⼀个视图为多个业务服务，⽽放进不需要的语句、表和字段。视图不要嵌套视图，效率会被⼤⼤影响，和join⼀样，也不要超过3个表。太复杂的，可以考虑⾛存储过程，效率会更⾼。活字格中不能直接建⽴外联表的视图，需要在数据库IDE中建⽴，导⼊时需要选择主键，SQL⾥视图是没有主键概念的，但既然它让你设，你就设⼀
个吧，据说能提⾼性能。⽽且，如果你不设，表名旁边的图标会显⽰为和普通表⼀样，⽽且不是视图特有的图标，完美主义者的我，是不能容忍这种事情的
8、关于外联表选mssql还是mysql，如果活字格服务端和数据库在同⼀个服务器，还是⾃⼰安装数据库，就⽤mssql，⼤多数⼈推荐
2008R2和2012，我已经升级到2012。这种情况选mssql的原因，⼀是阿⾥云和腾讯云亲测都可以安装使⽤，盗不盗版，如果不是做公有云布置，估计也没啥问题，⼆是windows环境还是⽤mssql，⽽且活字格是的，属于⼀家⼈；三是你⾃⼰下载安装的mysql和腾讯云提供给你的mysql是不⼀样的，⾃⼰安装的话，最好不选mysql。如果是使⽤阿⾥云或腾讯云的云数据库，那就选 mysql，⼀是更便宜，⼆是他们都做了优化，也有很多优化⼯具可以使⽤，⽐如前⾯的慢sql，不⽐mssql差。
之前下过mysql现在重新下载mysql9、数据库IDE，因为计划只⽤sqlserver，所以改⽤原⽣的IDE，SQLserver同⼀个安装包，安装时只选IDE就可以，可⽹上搜教程(2020-9-5改)
10、最后说说，我对选内置数据库还是外联数据库的看法。既然写了这么多外联数据库的东西，⾃然我的选择是外联数据库了。选什么不重要，重要的是为什么选？所以简单说⼀下原因：(1)百度sqlite，出现最多的关键词，是轻型、嵌⼊、桌⾯、不需要配置安装，然后sqlite ⽀持⾼并发的读，但不⽀持⾼并发的写，等等，我们是要⽤活字格来做企业业务应⽤的，⽽且是web应⽤，你会觉得sqlite的特点和
需求吻合吗？；(2)上⾯说了很多数据库优化的东西，⽆论是mssql还是mysql，都有⾮常多的优化教程、⽅法和⼯具，阿⾥云和腾讯云也都有提供专门的数据库优化⼯作和⽅案，我认为在数据量到百万甚⾄是⼏⼗万级的时候，性能上的表现⼀定会突出出来，⽽sqlite的优化空间太⼩；(3)接触到外联数据库，我才打开了⼀个新的窗⼝，才能写以上的东西。我相信很多⽤内置库的⽼铁们，表名字段名还是⽤中⽂吧，你不⽤外联库，你就根本不会关注到这些东西；(4)外联mysql和mssql都可以直联，这样可以⽅便的相互窜门，⽽sqlite虽然是活字格内置的，但却不能直连。
PS：虽然我选择了外联数据库，但我仍然会经常⽤到内置库，⽐如前⾯说的，在⼀些临时表和中间表的使⽤上，我基本上都是⽤sqlite，很多情况下，执⾏效率⽐外联表⾼很多，这样你的外联库看上去也清爽些。所以，活字格的内置库⽤sqlite我觉得挺好的，内置和外联，相互补充。另外，新接触低代码系统的⽼铁们，进⼊门槛也⼤⼤降低了~~~
11、看了⼏篇关于mysql和mssql安全性能的⽐较，以及云开发中云函数可以外接mysql，再加上两者云数据库相关两三倍的价格，⼼⾎来潮，花了⼀天时间捣⿎mysql，结果踩了坑。测试的环境，包括(1)使⽤腾讯云数据库mysql，与安装活字格的腾讯云服务器在⼀个私有⽹络内连结；(2)直接在安装活字格安装腾讯云服务器上⾃建mysql。原来环境是在腾讯云服务器中⾃建mssql。结果两个mysql环境效率，都⽐mssql环境，低了2倍左右。这个结果，不能说mysql的执⾏效率⽐mssql低，但⾄少可以下结论：活字格中，使⽤mssql⽐使⽤mysql 的效率更⾼。所以使⽤外联数据库，还是好好把mssql⽤好。
附两个测试点：(1)页⾯表格500⾏数据，使⽤提交表格命令，mssql>2-3分钟左右，mysql>6-8分钟
(2)页⾯两个表格，根据单元格值变化执⾏查询命令，mssql>1秒左右，mysql>3-5秒
12、另外，也总结⼀下使⽤mysql的注意事项：
(1)字符集选择utf8或utf8mb4，腾讯云官⽅是建议直接⽤uft8mb4，能⽀持表情字符，因为很多⼈的名会⽤表情字符。这两种字符集，排序规则都⽤general_ci。这个和mssql不⼀样，在mssql⾥，没有字符集的选择，是通过选择排序规则来选择的，选
Chinese_PRC_CI_AS
(2)是否，⽤tinyint(1)
(3)补充⼀下⼏个⼩数float、double和decimal的应⽤，三个精度依次提升，但效率也依次降低，和前⽂说的有不⼀样的地⽅。A、⾦融相关的，⼀定⽤decimal，⼤⼩⼀般设置为(18,2)，不⽤decimal，会遇到浮点不等情况；B、float效率最快，但只有7个有效位，⽐如你设置为(7，4)的话，最多只能表⽰***.****， ⼀些参数型、百分率的字段我会⽤到；C、double翻⼀倍，15个有效位
(4)mysql的数据类型更少，⽐如没有nvarchar(max)、varchar(max)这样的，所以在mysql⾥，长⽂本要⽤text
(5)活字格的图⽚和附件转到mysql，是⽤varchar(500)，多附件这种，可能会遇到字符长度不够的情况，如果预计不够，会要改⼀下。不过⾄今我还是没搞清楚，究竟varchar最长可以放多少个字符，资料，有说255的
(5)mysql⾥没有设置唯⼀键的地⽅，是通过建唯⼀索引来设置唯⼀键，索引类型⼀般选unique或normal(index)，索引⽅法⼀般BTREE。主键会⾃动建⼀个索引

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。