精华内容
下载资源
问答
  • MySQL主键

    千次阅读 2018-06-27 11:21:50
    MySQL主键设计 一. MySQL主键设计原则 MySQL主键应当是对用户没有意义的。 MySQL主键应该是单列的,以便提高连接和筛选操作的效率(当然复合主键是可以的,只是不建议) 永远也不要更新MySQL主键 MySQL主键不应...

    MySQL主键

    一. MySQL主键设计原则

    • MySQL主键应当是对用户没有意义的。
    • MySQL主键应该是单列的,以便提高连接和筛选操作的效率(当然复合主键是可以的,只是不建议)
    • 永远也不要更新MySQL主键
    • MySQL主键不应包含动态变化的数据,如时间戳、创建时间列、修改时间列等
    • MySQL主键应当有计算机自动生成。

    二. 主键添加方法

    • 在创建表的时候,直接在字段之后,添加primary key关键字
    create table my_pri1(
        name varchar(20) not null comment '姓名',
        number char(10) primary key comment '学号'
    )charset utf8;
    • 在创建表的时候,在所有的字段之后,使用primary key(主键字段列表)来创建主键,此处可用来添加复合主键(不建议)。
    create table my_pri2(
        number char(10) not null comment '学号',
        course char(10) not null comment '课程编号',
        score tinyint unsigned default 60,
        -- 增加主键限制,学号和课程编号应该是对应的,具有唯一性
        primary key(number)  //primary key(number,course)
    )charset utf8;
    • 当表创建完之后,额外追加主键,可以直接追加主键,也可以通过修改表字段的属性追加主键
    alter table my_pri3 modify course char(10) primary key comment '课程编号'; -- 不建议使用
    alter table my_pri3 add primary key(course); -- 推荐使用

    三. 主键设计的常用方案

    1. 自增ID

    优点:
    1) 数据库自动编号,速度快,而且是增量增长,聚集型主键按顺序存放,对于检索非常有利。

    2) 数字型,占用空间小,易排序,在程序中传递方便。

    缺点:
    1) 不支持水平分片架构,水平分片的设计当中,这种方法显然不能保证全局唯一。

    2) 表锁,在MySQL5.1.22之前,InnoDB自增值是通过其本身的自增长计数器来获取值,该实现方式是通过表锁机制来完成的(AUTO-INC LOCKING)。锁不是在每次事务完成后释放,而是在完成对自增长值插入的SQL语句后释放,要等待其释放才能进行后续操作。比如说当表里有一个auto_increment字段的时候,innoDB会在内存里保存一个计数器用来记录auto_increment的值,当插入一个新行数据时,就会用一个表锁来锁住这个计数器,直到插入结束。如果大量的并发插入,表锁会引起SQL堵塞。在5.1.22之后,InnoDB为了解决自增主键锁表的问题,引入了参数innodb_autoinc_lock_mode:

    • 0:通过表锁的方式进行,也就是所有类型的insert都用AUTO-inc locking(表锁机制)。
    • 1:默认值,对于simple insert 自增长值的产生使用互斥量对内存中的计数器进行累加操作,对于bulk insert 则还是使用表锁的方式进行。
    • 2:对所有的insert-like 自增长值的产生使用互斥量机制完成,性能最高,并发插入可能导致自增值不连续,可能会导致Statement 的 Replication 出现不一致,使用该模式,需要用 Row Replication的模式。

    3) 自增主键不连续,对表增加属性AUTO_INCREMENT=16时,主键就会从16开始自增起;实际的插入数据操作中,可能存在不连续的主键值。

    2. UUID

    优点:
    1) 全局唯一性、安全性、可移植性。

    2) 能够保证独立性,程序可以在不同的数据库间迁移,效果不受影响。

    3) 保证生成的ID不仅是表独立的,而且是库独立的,在你切分数据库的时候尤为重要

    缺点:
    1) 针对InnoDB引擎会徒增IO压力,InnoDB为聚集主键类型的引擎,数据会按照主键进行排序,由于UUID的无序性,InnoDB会产生巨大的IO压力。InnoDB主键索引和数据存储位置相关(簇类索引),uuid 主键可能会引起数据位置频繁变动,严重影响性能。

    2) UUID长度过长,一个UUID占用128个比特(16个字节)。主键索引KeyLength长度过大,而影响能够基于内存的索引记录数量,进而影响基于内存的索引命中率,而基于硬盘进行索引查询性能很差。严重影响数据库服务器整体的性能表现。

    3.自定义序列

    自定义序列列表:就是在库中建一张用于生成序列的表来存储序列信息,序列生成的策略通过程序层面来实现。如下所示,构建一张序列表:

    CREATE TABLE `sequence` (
        `name` varchar(50) NOT NULL,
        `id` bigint(20) unsigned NOT NULL DEFAULT '0',
        PRIMARY KEY (`name`)
    ) ENGINE=InnoDB;

    注意区别,id字段不是自增的,也不是主键。在使用前,我们需要先插入一些初始化数据:

    INSERT INTO `sequence` (`name`) VALUES 
    ('users'), ('photos'), ('albums'), ('comments');

    接下来,我们可以通过执行下面的SQL语句来获得新的照片ID:

    UPDATE `sequence` SET `id` = LAST_INSERT_ID(`id` + 1) WHERE `name` = 'photos';
    SELECT LAST_INSERT_ID();

    我们执行了一个更新操作,将id字段增加1,并将增加后的值传递到LAST_INSERT_ID函数, 从而指定了LAST_INSERT_ID的返回值。
    实际上,我们不一定需要预先指定序列的名字。如果我们现在需要一种新的序列,我们可以直接执行下面的SQL语句:

    INSERT INTO `sequence` (`name`) VALUES('new_business') ON DUPLICATE KEY UPDATE `id` = LAST_INSERT_ID(`id` + 1);
    SELECT LAST_INSERT_ID();

    这种方案的问题在于序列生成的逻辑脱离了数据库层,由应用层负责,增加了开发复杂度。不过一般序列值都是通过Java的框架去自动生成的,具体就不在MySQL系列文章阐述。

    四. 如何解决水平分片的需求

    UUID

    由于UUID出现重复的概率基本可以忽略,所以对分片是天生支持的。

    独立的序列库

    单独建立一个库用来生成ID,在Shard中的每张表在这个ID库中都有一个对应的表,而这个对应的表只有一个字段, 这个字段是自增的。当我们需要插入新的数据,我们首先在ID库中的相应表中插入一条记录,以此得到一个新的ID, 然后将这个ID作为插入到Shard中的数据的主键。这个方法的缺点就是需要额外的插入操作,如果ID库变的很大, 性能也会随之降低。所以一定要保证ID库的数据集不要太大,一个办法是定期清理前面的记录。

    复合标识符

    这种做法是通过联合主键的策略,即通过两个字段来生成一个唯一标识,前半部分是分片标识符,后半部分是本地生成的标识符(比如使用AUTO_INCREMENT生成)。

    带分库策略的自定义序列表

    这种做法可以基于上面提到的自定义序列表的方法的基础上,做一些技巧性的调整。即如下:

    UPDATE `sequence` SET `id` = LAST_INSERT_ID(`id` + 1) WHERE `name` = 'photos';
    SELECT LAST_INSERT_ID();

    这里的id初始值设定上要求不同的分片取不同的值,且必须连续。同时将每次递增的步长设定为服务器数目。
    比如有3台机器,那么我们只要将初始值分别设置为1,2,3. 然后执行下面的语句即可:

    UPDATE `sequence` SET `id` = LAST_INSERT_ID(`id` + 3) WHERE `name` = 'photos';
    SELECT LAST_INSERT_ID();

    这就可以解决主键生成冲突的问题。但是如果在运行一段时间后要进行动态扩充分片数的时候,需要对序列初始值做一次调整,以确保其连续性,否则依然可能存在冲突的可能。当然这些逻辑可以封装在数据访问层的代码中。

    五. 主键的必要性

    表中每一行都应该有可以唯一标识自己的一列(或一组列)。虽然并不总是都需要主键,但大多数数据库设计人员都应保证他们创建的每个表有一个主键,以便于以后数据操纵和管理。其实即使你不建主键,MySQL(InnoDB引擎)也会自己建立一个隐藏6字节的ROWID作为主键列,详细可以参见[这里]

    因为,InnoDB引擎使用聚集索引,数据记录本身被存于主索引(一颗B+Tree)的叶子节点上。这就要求同一个叶子节点内(大小为一个内存页或磁盘页)的各条数据记录按主键顺序存放,因此每当有一条新的记录插入时,MySQL 会根据其主键将其插入适当的节点和位置,如果页面达到装载因子(InnoDB默认为15/16),则开辟一个新的页(节点)
    这里写图片描述
    所以在使用innoDB表时要避免随机的(不连续且值的分布范围非常大)聚簇索引,特别是针对I/O密集型的应用。例如:从性能角度考虑,使用UUID的方案就会导致聚簇索引的插入变得完全随机。

    主键的数据类型选择

    关于主键的类型选择上最常见的争论是用整型还是字符型的问题,关于这个问题《高性能MySQL》一书中有明确论断:
    整数通常是标识列的最好选择,因为它很快且可以使用AUTO_INCREAMENT,如果可能,应该避免使用字符串类型作为标识列,因为很消耗空间,且通常比数字类型慢。

    如果是使用MyISAM,则就更不能用字符型,因为MyISAM默认会对字符型采用压缩引擎,从而导致查询变得非常慢。

    展开全文
  • Navicat Navicat中怎么设置主键自增长?

    Navicat  Navicat   中怎么设置主键自增长? mysql

    点击设计表,然后

    出现如下菜单

    展开全文
  • mysql 主键分区

    2019-06-24 20:50:39
    mysql 主键分区 通常对于数据比较多的表,可以采用字段分区,一般是id,首先需要把id指定为bigint(20)类型,然后进行id分区 ALTER TABLE `sh_ad_hit` PARTITION BY RANGE (id) PARTITIONS 21 (PARTITION `p0` VALUES...

    mysql 主键分区

    通常对于数据比较多的表,可以采用字段分区,一般是id,首先需要把id指定为bigint(20)类型,然后进行id分区

    ALTER TABLE `sh_ad_hit` PARTITION BY RANGE (id)
    PARTITIONS 21
    (PARTITION `p0` VALUES LESS THAN (10000000),
    PARTITION `p1` VALUES LESS THAN (20000000),
    PARTITION `p2` VALUES LESS THAN (30000000),
    PARTITION `p3` VALUES LESS THAN (40000000),
    PARTITION `p4` VALUES LESS THAN (50000000),
    PARTITION `p5` VALUES LESS THAN (60000000),
    PARTITION `p6` VALUES LESS THAN (70000000),
    PARTITION `p7` VALUES LESS THAN (80000000),
    PARTITION `p8` VALUES LESS THAN (90000000),
    PARTITION `p9` VALUES LESS THAN (100000000),
    PARTITION `p10` VALUES LESS THAN (110000000),
    PARTITION `p11` VALUES LESS THAN (120000000),
    PARTITION `p12` VALUES LESS THAN (130000000),
    PARTITION `p13` VALUES LESS THAN (140000000),
    PARTITION `p14` VALUES LESS THAN (150000000),
    PARTITION `p15` VALUES LESS THAN (160000000),
    PARTITION `p16` VALUES LESS THAN (170000000),
    PARTITION `p17` VALUES LESS THAN (180000000),
    PARTITION `p18` VALUES LESS THAN (190000000),
    PARTITION `p19` VALUES LESS THAN (200000000),
    PARTITION `p20` VALUES LESS THAN (MAXVALUE) 
    );
    

    这里是做了20个分区,注意最后一个分区是为了防止数据填满了p19从而无法继续插入数据而做的分区,指定了MAXVALUE就不会受p19限制,而会限制在该字段能承受的最大值。

    展开全文
  • java取得Mysql主键,在Mysql获取表中所有的主键

    Json数据在同步的时候需要取得对应数据中的主键,但是mysql没有找到获得主键名的方法,所以自己写了一个获取主键的方法,并且能正确使用,代码如下

             /**
    	 * 根据数据库连接和表明获取主键名
    	 * @param con 传进来一个数据库连接对象
    	 * @param table 数据库中的表名
    	 * @return  执行成功返回一个主键名的字符数组,否则返回null或抛出一个异常
    	 * @exception 抛出sql执行异常
    	 * @author yuyu
    	 */
    	public static String[] getPrimaryKey(Connection con,String table)throws Exception{
    		
    		String sql="SHOW CREATE TABLE "+table;
    		
    		try {
    	
    			PreparedStatement pre=con.prepareStatement(sql);
    			ResultSet rs=pre.executeQuery();
    			if(rs.next()){
    
    				//正则匹配数据
    				Pattern pattern = Pattern.compile("PRIMARY KEY \\(\\`(.*)\\`\\)");
    				Matcher matcher =pattern.matcher(rs.getString(2));
    				matcher.find();
    				String data=matcher.group();
    				//过滤对于字符
    				data=data.replaceAll("\\`|PRIMARY KEY \\(|\\)", "");
    				//拆分字符
    				String [] stringArr= data.split(",");
    				
    				return stringArr;
    			}
    			
    		}catch(Exception e){
    			throw e;
    		}
    		return null;
    	}


    展开全文
  • Mysql主键UUID和自增主键区别优劣

    万次阅读 2018-09-04 09:31:24
    最近用mysql,发现mysql主键都是选择自增主键,仔细比较一下,为什么mysql选择自增主键,有什么不同。 在mysql5.0之前,如果是多个master复制的环境,无法用自增主键,因为可能重复。在5.0以及之后的版本通过配置...
  • mysql主键id自定义

    万次阅读 2020-12-29 11:42:02
    执行以下sql即可 alter table user(表名) AUTO_INCREMENT=800008(想要主键id从哪开始);
  • Mysql主键选择之UUID和自增主键

    万次阅读 2018-03-29 11:44:03
    最近用mysql,发现mysql主键都是选择自增主键,仔细比较一下,为什么mysql选择自增主键,有什么不同。在mysql5.0之前,如果是多个master复制的环境,无法用自增主键,因为可能重复。在5.0以及之后的版本通过配置自增...
  • MySql 主键和外键怎么使用?

    千次阅读 2020-07-30 22:12:20
    MySql主键&外键 主键用法&意义 主键,是表中一列或者多列的组合,主键约束(Primary Key Constraint)要求主键列的数据唯一,并且不允许为空,主键能够唯一的标识表中的每一条记录,可以结合外键来定义与...
  • mysql主键唯一键重复插入解决方法

    千次阅读 2017-12-20 13:53:03
    mysql主键唯一键重复插入解决方法
  • 浅谈MySQL主键

    千次阅读 2016-01-04 15:13:53
    浅谈MySQL主键 主键没有着明确的概念定义,其是索引的一种,并且是唯一性索引的一种,且必须定义为“PRIMARY KEY”,是只可意会不可言传的东西。下面让我用通俗,甚至有些低俗的语言为您简单介绍一下...
  • MySQL 主键与事务

    千次阅读 多人点赞 2021-05-13 15:13:44
    MySQL 主键与事务 一、MySQL 主键和表字段的注释 1.主键及自增 2.表字段的注释 3.多表查询 二、数据库事务概述 1.数据库事务特性(ACID) 2.事务并发不进行事务隔离 4.MySQL 数据库管理事务 1)别名 2)去重 3)AND ...
  • MySQL 主键索引长度不够怎么解决! 环境:Linux Center OS 目标:安装 james-server-app-3.4.0 邮件服务 数据库: MySql 8.0.18 james 启动过程创建数据表结构抛出异常: Caused by: org.apache.openjpa.lib.jdbc....
  • [mysql主键、外键约束]表创建完成后,再添加主键约束 今天在做数据库作业时,发现了问题,我不小心忘了写主键约束,查了书籍,全都是在插入数据时写的约束,上网找到了一个解决方案试验后找到了它的公式。 alter ...
  • mysql 安装、sql语法 一条sql语句完成MySQL去重留一 ... MySQL主键、创建索引、UNION 和 UNION ALL 主键 索引 ALTER TABLE 表名 ADD INDEX 索引名称 (一个或多个字段名(字段的长度size)) ...
  • Mybatis+mysql主键自增长,插入语句返回主键id #(一)SQL语句获取主键自增长的id <insert id="addGoods" parameterType="com.wlmall.shop.domain.Goods"> <-/ 通过此语句...
  • MySQL 主键自增,会出现跳键

    千次阅读 2019-01-24 11:16:43
    MySQL 主键自增,会出现跳键 这个表记录的是设备的心跳数据,记录设备第一次上传心跳的时间和最后一次上传数据的时间 我的sql: insert into tb_ap_heart (ap_id, create_time, update_time) values (#{apId,...
  • MySQL 主键详解

    千次阅读 2018-12-05 19:19:04
    MYSQL中整型范围: 类型 大小 范围(有符号) 范围(无符号) 用途 TINYINT 1 字节 (-128,127) (0,255) 小整数值 SMALLINT 2 字节 (-32 768,32 767) (0,65 535) 大整数值 MEDIUMINT 3 字节 (-8 388 ...
  • MYSQL主键策略(自增,UUid,雪花算法) 作为一个快要大四的学生,mysql的主键策略到底怎么选用一直很疑惑,平时也会查阅一些资料,但都没有整合思考过,今天趁着有时间就写一写我知道的一些东西。 自增的优点: 1....
  • mysql 主键 索引

    千次阅读 2017-02-03 11:48:20
    http://www.cnblogs.com/huangye-dream/archive/2013/06/03/3115262.html1.PRIMARY KEY(主键索引) mysql>ALTER TABLE table_name ADD PRIMARY KEY ( column ) 2.UNIQUE(唯一索引) mysql>ALT
  • MySQL主键的理解

    千次阅读 2016-01-20 16:14:18
    Mysql中规定自增列必须为主键,在插入时,如果自动增长列没有输入值,则插入的值为自动增长后的值;如果输入的值为0或者null,则插入的值也为自动增长后的值;如果插入某个确定的值,且该值在前面没有出现过,则可以...
  • MySQL 主键约束

    千次阅读 2019-01-30 17:00:19
    主键,又称住码,是表中一列或多列的组合。 主键要求主键列的数据唯一,并且不允许为空,主键能够唯一地表识表中的一条记录, 可以结合外键定义不同数据表之间的关系,并且可以加快数据库查询的速度。主键和 记录...
  • 如何让MySQL主键自增从1开始,或者从固定值开始 ALTER TABLE 表名 AUTO_INCREMENT=1; 1为你主键id自增起始值
  • MySQL主键的自增

    千次阅读 2018-10-13 22:11:04
    MySQL中的主键必须设置自增属性吗? 答案是 “No” 主键不一定自增,但自增的一定要是主键。相反:设置自增属性的列必须是主键 或者加UNIQUE索引 主键是有唯一性的 即不可以重复输入相同的值 还有一点:主键不能...
  • mybatis mysql 主键自增加怎么配置

    千次阅读 2018-02-05 16:12:16
    mybatis mysql 主键自增加怎么配置我用这个解决了:https://www.cnblogs.com/zhuzhen/p/6894995.htmlhttp://blog.csdn.net/WSRspirit/article/details/51260240mybatis进行插入操作时,如果表的主键是自增的,针对...
  • PowerDesigner 15设置mysql主键自动增长及基数 1、双击标示图,打开table properties->columns, 如图点击图标Customize Columns and Filter(或直接用快捷键Ctrl+U) 2、选中identity 3、 设置基数,点击physical...
  • mysql 主键选择

    千次阅读 2016-06-28 21:32:07
    (针对InnoDB引擎)我们实际生产环境可能会使用四类属性作为主键:(1). 自增序列;(2). UUID()函数生成的随机值;(3). 用户注册的唯一性帐号名称,字符串类型,一般长度为:40个字符;(4). 基于一套机制生成类似自增的...
  • 设置了mysql主键自动增长,但因为删除字段的操作导致主键不连续 解决方法 step1:在mapper.xml文件中添加update标签设置自动增长的增量为1 alter table student AUTO_INCREMENT=1; <!--StudentMapper.xml文件--&...
  • mysql主键设置自动增长,删除几条记录后,新增的主键不是现在主键的下一个,而是所有记录中的下一个,求怎样获取下一个主键值
  • Mysql主键索引与非主键索引区别

    千次阅读 2020-05-05 01:18:05
    MySql官方索引的定义:索引(Index)是帮助MySql高效获取数据的数据结构,索引的目的在于提高查询效率,类比字典;实际上索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,索引列也是要占用空间。 ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 275,540
精华内容 110,216
关键字:

mysql主键

mysql 订阅