锐客网

  1. 首页 > 睿知 > it技术 > >

mysql主键id怎么设计的 mysql主键的建立3种方法

IT技术一个

MySql 设置ID主键自增,从0开始,请问怎么设?就mysql主键id怎么设计的我所知这个好像无法实现mysql主键id怎么设计的，就算你自己编译mysql恐怕也很难 。因为last_insert_id()这个内置的函数返回最近一次插入的自增长ID，当没有符合条件的ID时，它返回0，所以如果自增长从0开始的话，这个返回值就很难判定到底是插入了一条自增长ID为0的记录还是没有符合条件的记录了 。
如何在数据库中设置ID字段为主键在我看来,这种设计,是为了保证,在各种数据库上面,都兼容.
因为对于 自增ID 的处理 。各个厂商的实现方式都不一样
例如
SQL Server 是IDENTITY 实现自增
Oracle 是 SEQUENCE触发器 实现自增
MySQL 是 AUTO_INCREMENT 实现自增
对于一家软件公司来说mysql主键id怎么设计的，如果他的产品，仅仅只能运行在 SQL Server 数据库上 。
那么如果客户已经购买了 Oracle ， mysql主键id怎么设计的你要客户购买mysql主键id怎么设计的你的软件 ， 然后再买套 SQL Server 。
是不现实的 。
如果你的设计中，过分的使用了数据库的特性，那么，对于你的产品 。
比如 For SQL Server 版的与For Oracle 版的 。
差异的地方，就很多，代码修改量也大 。出错率也高 。
比如某个功能 SQL Server 上面运行正常 。
但是 Oracle 上面出错了 。
结果很可能就是
SQL Server 里面的 IDENTITY 处理正常 。
很可能 Oracle 那里，触发器 或者 SEQUENCE 忘记创建，或者创建失败了 。最后导致错误 。
但是
每个表字段ID不设置为主键 建立一个TableID 有个2个字段 TableName ， ID
这种方式，对所有的 数据库，都支持 。
不需要特别的设定 。
我的印象中，好像 Java 开发中，使用的 Hibernate 里面 ， 就是这么设定的 。
有个表专门存储 表名 与 ID 的 。
而且 Hibernate有它特有的处理机制的 。
如果非常频繁的插入操作 。比如现在 A 表的 ID 是 100
然后同一时刻，插入了 20条记录
那么 处理完毕后，A表的 ID 直接由 100 变为 120
而不是 从 100 开始，检索20次，更新 20次，更新到 120 。
这样也没有性能方面上的问题 。
技术分享 | 关于 MySQL 自增 ID 的事儿当我们使用 MySQL 进行数据存储时，一般会为一张表设置一个自增主键 ， 当有数据行插入时，该主键字段则会根据步长与偏移量增长（默认每次 1） 。
下文以 Innodb 引擎为主进行介绍，使用自增主键的好处有很多，如：索引空间占比小、范围查询与排序都友好、避免像 UUID 这样随机字符串带来的页分裂问题等...
当我们对该表设置了自增主键之后 ， 则会在该表上产生一个计数器，用于为自增列分配 ID。
自增的值并不是保存在表结构信息内的，对于不同的版本它们有如下的区别：
计数器的值存储在内存中的，重启后丢弃，下一次将读取最大的一个自增ID往后继续发号 。
计数器的值将会持久化到磁盘 。在每次发号时都将写入 Redolog ，并在每个 Checkpoint 都进行保存，重启时候使用 Redolog 恢复重启之前的值 。
可以预先确定插入行数的语句（像简单 insert 的语句包含多个 value 这种情况也是属于简单插入，因为在进行插入时就已经可以确定行数了）
【mysql主键id怎么设计的 mysql主键的建立3种方法】 预先不知道要插入的行数的语句（包括 INSERT ... SELECT, REPLACE ... SELECT 和 LOAD DATA 语句，但不包括 plain INSERT ）
如果一个事务正在向表中插入值 ， 则会产生表级的共享锁 ， 以便当前事务插入的行接收连续的主键值 。
当处于[ 传统模式 ]与[ 连续模式 ]时，每次访问计数器时都会加上一个名为 AUTO-INC 的表级锁
传统模式：锁只持有到该语句执行结束，注意是语句结束，不是事务结束
连续模式：批量插入时锁持有到该语句执行结束 ， 简单插入时锁持有到申请完自增ID后即释放 ， 不直到语句完成
通过调整 innodb_autoinc_lock_mode 配置项 ， 可以定义 AUTO-INC 锁的模式 ， 不同的模式对应的策略与锁的粒度也将不同 。
当使用基于 Binlog 的复制场景时，对于 statement（SBR）同步模式下只有[ 传统模式 ]与[ 连续模式 ]能保证语句的正确性 。
基于 row（RBR）行复制的情况下任何配置模式都可以 。
执行语句时加 AUTO-INC 表级锁，执行完毕后释放
针对 Bulk Inserts 时才会采用 AUTO-INC 锁，而针对 Simple Inserts 时，则采用了一种新的轻量级的互斥锁来分配 auto_increment 列的值 。
该模式下可以保证同一条 insert 语句中新插入的自增 ID 都是连续的，但如果前一个事务 rollback 丢弃了一部分 ID 的话也会存在后续 ID 出现间隔的情况 。
来一个分配一个 ， 不会产生 AUTO-INC 表级锁，仅仅会锁住分配 ID 的过程 。
由于锁的粒度减少，多条语句在插入时进行锁竞争，自增长的值可能不是连续的 。
且当 Binlog 模式为 statement（SBR）时自增 ID 不能保证数据的正确性
不一定，业务也不应该过分依赖 MySQL 自增 ID 的连续性，在以下三种情况下，并不能保证自增 ID 的连续性：
假设已存在数据{1,张三}，且张三所属的字段设置了唯一主键
此时再次插入{null,张三}时候，主键冲突插入失败，但表的计数器已由2变成了3
当下次插入{null,李四}的时候最终入库的会变成{3,李四}
在一个事务里进行数据的插入，但最后并没提交，而是执行了 Rollback。那么计数器已递增的 ID 是不会返还的 ， 而是被直接丢弃 。
发生大量插入时可能会出现自增 ID 并不是连续的情况
当我们为表设置了自增主键后 ， 自增 ID 的范围则与主键的数据类型长度相关 。
如果没有一张表里没有设置任何主键，则会自动生成一个隐性的6字节的 row_id 作为主键 ， 它的取值范围为 0 到 2^48-1 。
row_id 是由一个全局的 dict_sys.row_id 参数进行维护的，所有没有主键的表都会用上它（并不是每一个表单独占一份 row_id list ）
那么针对这两种主键，则会有以下两种情况发生：
当自增 ID 到达上限后，受到主键数据类型的影响，计数器发放的下一个 ID 也是当前这个 Max ID ， 当执行语句时则会提示主键冲突 。
建议根据业务合理规划，在进行表设计时就选择适合的数据类型 。
当然也可以直接选择 Bigint 类型，它的取值范围是无符号情况下：0到 2^64–1（18446744073709551615）
这里并不是指 bigint 类型一定不会用完，毕竟一个有范围的持续增长的值一定会有溢出的时候，只是说一般场景下它都是足够使用的 。
当 row_id 使用完后则又会从 0 开始发放，此时新插入的数据将覆盖回 row_id=0 的数据行 。
由于它并不产生错误 ， 还会造成数据的覆盖写 。所以我们平时还是尽量给表都设置一个合理的主键才是 。
在实际业务场景中，ID 常常需要返回给客户端用来进行相关业务操作 。
假如我们有个 userinfo?uid=? 的 API 接口，而用户 ID 是自增的，这时会发生什么mysql主键id怎么设计的？
该接口通过简单的尝试就可以暴露出真实的业务用户总数，可以很方便的使用爬虫从1开始递增获取数据信息 。
那么有的同学说，我既想使用自增 ID 带来的好处，也不想承受这种比较常见的问题，那该怎么办呢？
在输出或者获取前对指定字段进行可逆的转义操作
优点：实现起来比较简单，无论单体业务或者分布式应用都无需考虑对数据源的解析，只需在客户端实现自己的转义与解析方法即可mysql主键id怎么设计的；
缺点：业务入侵较大，且需要前后端各个合作方确认统一的标准；如果转义方法有调整，变更影响面也会很大；字符串长度会随ID长度而变化，使用空位填充也会特别明显；
优点：由于采用了时间戳进行 ID 生成，该 ID 是有序的，对范围查询与排序都比较友好；
缺点：需要保证发号节点的高可用性；另外由于生成时依赖时间戳，需要考虑时钟回拨与时钟同步的问题；
维护一份 ID 与 hash 的映射字典，它可以存在于客户端本身，也可以依赖其mysql主键id怎么设计的他如 Redis 、ETCD 之类的组件
优点：hash 长度不会随着 ID 长度或值的变化而变化；可以根据已有的 hash code 来造布隆过滤器；
缺点：业务入侵较大，查询时同样需要先根据 hash key 找到对应的 ID 值；需要考虑选择合适的 hash 算法以及解决 hash 冲突或扩容的问题 。
mysql数据库表用什么做主键？1、主键定义
表中经常有一个列或多列的组合，其值能唯一地标识表中的每一行 。这样的一列或多列称为表的主键，通过它可强制表的实体完整性 。当创建或更改表时可通过定义 PRIMARY KEY 约束来创建主键 。一个表只能有一个 PRIMARY KEY 约束，而且 PRIMARY KEY 约束中的列不能接受空值 。由于 PRIMARY KEY 约束确保唯一数据，所以经常用来定义标识列 。
2、主键作用
1）保证实体的完整性;
2）加快数据库的操作速度
3） 在表中添加新记录时，数据库会自动检查新记录的主键值，不允许该值与其他记录的主键值重复 。
4） 数据库自动按主键值的顺序显示表中的记录 。如果没有定义主键 ， 则按输入记录的顺序显示表中的记录 。
3、主键的必要性
在有些数据库中，虽然主键不是必需的 ， 但最好为每个表都设置一个主键，不管是单主键还是复合主键 。它存在代表着表结构的完整性，表的记录必须得有唯一区分的字段，主键主要是用于其他表的外键关联，以及本记录的修改与删除 。
4、主键的选择
1）编号作主键
此方法就是采用实际业务中的唯一字段的“编号”作为主键设计，这在小型的项目中是推荐这样做的，因为这可以使项目比较简单化，但在使用中却可能带来一些麻烦，比如要进行“编号修改”时，可能要涉及到很多相关联的其他表
2）自动编号主键
这种方法也是很多人在使用的，就是新建一个ID字段 ， 自动增长，非常方便也满足主键的原则
a、优点：数据库自动编号，速度快 ， 而且是增量增长，聚集型主键按顺序存放，对于检索非常有利;数字型的 ， 占用空间?。?易排序，在程序中传递也方便;如果通过非系统增加记录（比如手动录入，或是用其他工具直接在表里插入新记录，或老系统数据导入）时，非常方便，不用担心主键重复问题 。
b、缺点：其实缺点也就是来自其优点，就是因为自动增长，在手动要插入指定ID的记录时会显得麻烦 ， 尤其是当系统与其他系统集成时，需要数据导入时，很难保证原系统的ID不发生主键冲突（前提是老系统也是数字型的）;如果其他系统主键不是数字型那就麻烦更大了，会导致修改主键数据类型了，这也会导致其他相关表的修改，后果同样很严重;就算其他系统也是数字型的，在导入时，为了区分新老数据，可能想在老数据主键前统一加一个“o”(old)来表示这是老数据，那么自动增长的数字型又面临一个挑战 。
3）Max加一
由于自动编号存在那些问题，所以有些人就采用自己生成，同样是数字型的，只是把自动增长去掉了，采用在Insert时，读取Max值后加一，这种方法可以避免自动编号的问题，但也存在一个效率问题，如果记录非常大的话，那么Max()也会影响效率的;更严重的是并发性问题，如果同时有两人读到相同的Max后，加一后插入的ID值会重复，这已经是有经验教训的了 。
4）自制加一
考虑Max加一的效率后，有人采用自制加一，也就是建一个特别的表，字段为：表名，当前序列值 。这样在往表中插入值时，先从此表中找到相应表的最大值后加一，进行插入，有人可能发现，也可能会存在并发处理 ， 这个并发处理，可以采用lock线程的方式来避免，在生成此值的时，先Lock，取到值以后，再unLock出来，这样不会有两人同时生成了 。这比Max加一的速度要快多了 。但同样存在一个问题：在与其他系统集成时，脱离了系统中的生成方法后，很麻烦保证自制表中的最大值与导入后的保持一致 ， 而且数字型都存在上面讲到的“o”老数据的导入问题 。因此在“自制加一”中可以把主键设为字符型的 。字符型的自制加一中应该字符型主键可以应付很多我们意想不到的情况 。
5）GUID主键
目前一个比较好的主键是采用GUID，推荐主键还是字符型的，但值由GUID生成，GUID是可以自动生成，也可以程序生成，而且键值不可能重复 ， 可以解决系统集成问题 ， 几个系统的GUID值导到一起时，也不会发生重复，就算有“o”老数据也可以区分 ， 而且效率很高，在.NET里可以直接使用System.Guid.NewGuid()进行生成，在SQL里也可以使用NewID()生成 。优点是：
同IDENTITY 列相比，uniqueidentifier 列可以通过NewID() 函数提前得知新增加的行ID，为应用程序的后续处理提供了很大方便 。
a、优点：便于数据库移植，其它数据库中并不一定具有IDENTITY 列，而Guid 列可以作为字符型列转换到其它数据库中，同时将应用程序中产生的GUID 值存入数据库，它不会对原有数据带来影响 。便于数据库初始化，如果应用程序要加载一些初始数据，IDENTITY 列的处理方式就比较麻烦，而uniqueidentifier 列则无需任何处理，直接用T-SQL 加载即可 。便于对某些对象或常量进行永久标识，如类的ClassID，对象的实例标识，UDDI 中的联系人、服务接口、tModel标识定义等 。
b：缺点是：
GUID 值较长，不容易记忆和输入，而且这个值是随机、无顺序的
GUID 的值有16 个字节，与其它那些诸如4 字节的整数相比要相对大一些 。这意味着如果在数据库中使用uniqueidentifier 键，可能会带来两方面的消极影响：存储空间增大；索引时间较慢 。
mysql创建数据库时怎么将主键设置为UUID，建表语句怎么写mysql设置UUID为主键需要先将数据类型设置为VARCHAR(36)，然后插入数据的时候用UUID函数插入UUID 。下面我用win10系统下的CMD命令行具体演示一下：
1、打开CMD，输入mysql -uroot -p命令连接mysql ， 如下图所示
2、通过create创建表，指定ID为varchar(36) ， 并设置为主键，如下图所示
3、在插入数据的时候 ， 调用uuid()函数往主键字段里插入uuid值，如下图所示
4、最后查询插入的数据就可以看到id主键列存放的是uuid类型了，如下图所示
mysql 中如何给已存在的表中字段增设置主键？1、打开navicat工具，连接上mysql服务器，选择完数据库之后，选择一个表右击选择设计表（这里为了演示测试，随便选择一个表即可） 。
2、在设计表页面，可以看到当前表的所有字段信息，我们选择的学生表有一个id字段 ， 目前该表没有主键字段 。
3、如图，在最后一列右击选择主键 ， 即可将该字段设置为主键，也可以直接点击鼠标左键，可以快速添加和取消主键 。
4、设置完主键之后 ， 可以看到一把锁的标志，并且有一个1字，因为一个表可以给多个字段添加主键，则为联合主键，这样就显示为主键1，主键2等 。
5、主键设置完成之后 ， 还没有自增，选择id字段之后，在下方 ， 如图，勾选自动递增，这样id在每次插入记录之后都会自增一个值 。
6、设置完成主键和自增之后，点击保存，关闭当前窗口，然后选择表名右击选择对象信息 。
7、在DLL页面中，可以看到刚刚添加的主键和自增的DLL语句，这里就是创建表的DLL语句 。
8、上面有提到联合主键 ， 其实一个表可以给多个字段设置主键，这样可以组成联合主键，对于特定的业务 ， 联合主键也是必须的 。
mysql主键id怎么设计的的介绍就聊到这里吧，感谢你花时间阅读本站内容，更多关于mysql主键的建立3种方法、mysql主键id怎么设计的的信息别忘了在本站进行查找喔 。

    推荐阅读


    上一篇：redis的持久化方式包括 redis的持久化方式

    下一篇：光纤怎么传输到电视上去，光纤转电视