下文以 Innodb 引擎为主进行介绍，使用自增主键的好处有很多，如：索引空间占比小、范围查询与排序都友好、避免像 UUID 这样随机字符串带来的页分裂问题等...
当我们对该表设置了自增主键之后，则会在该表上产生一个计数器，用于为自增列分配 ID 。
自增的值并不是保存在表结构信息内的，对于不同的版本它们有如下的区别：
计数器的值存储在内存中的，重启后丢弃，下一次将读取最大的一个自增ID往后继续发号。
https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html#innodb-auto-increment-initialization
计数器的值将会持久化到磁盘。在每次发号时都将写入 Redolog ，并在每个 Checkpoint 都进行保存，重启时候使用 Redolog 恢复重启之前的值。
https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html#innodb-auto-increment-initialization
可以预先确定插入行数的语句（像简单 insert 的语句包含多个 value 这种情况也是属于简单插入，因为在进行插入时就已经可以确定行数了）
预先不知道要插入的行数的语句（包括 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 是自增的，这时会发生什么？
该接口通过简单的尝试就可以暴露出真实的业务用户总数，可以很方便的使用爬虫从1开始递增获取数据信息。
那么有的同学说，我既想使用自增 ID 带来的好处，也不想承受这种比较常见的问题，那该怎么办呢？
在输出或者获取前对指定字段进行可逆的转义 *** 作
优点：实现起来比较简单，无论单体业务或者分布式应用都无需考虑对数据源的解析，只需在客户端实现自己的转义与解析方法即可；
缺点：业务入侵较大，且需要前后端各个合作方确认统一的标准；如果转义方法有调整，变更影响面也会很大；字符串长度会随ID长度而变化，使用空位填充也会特别明显；
优点：由于采用了时间戳进行 ID 生成，该 ID 是有序的，对范围查询与排序都比较友好；
缺点：需要保证发号节点的高可用性；另外由于生成时依赖时间戳，需要考虑时钟回拨与时钟同步的问题；
维护一份 ID 与 hash 的映射字典，它可以存在于客户端本身，也可以依赖其他如 Redis 、ETCD 之类的组件
优点：hash 长度不会随着 ID 长度或值的变化而变化；可以根据已有的 hash code 来造布隆过滤器；
缺点：业务入侵较大，查询时同样需要先根据 hash key 找到对应的 ID 值；需要考虑选择合适的 hash 算法以及解决 hash 冲突或扩容的问题。
select * from mytable where name like '张%' limit 3,5#查询 姓名为张 并且取 第三条到第5条的数据，
#我一般叫做分页查询 就是截取从第几条到第几条
Mysql *** 作
创建数据库
新建数据库命令：Createdatabase 数据库
查看所有数据库
命令：Show databases
打开数据库
命令：use 数据库名
删除数据库
命令：drop database 数据库名
创建表
命令：create table 表名（列名 数据类型，列名 数据类型，。。。。。。。）
查看当前数据库下所有表
命令：show tables
查看当前表结构
命令：desc 表名
删除表
命令：drop table 表名
插入数据insert
命令：Insert into 表名（字段1，字段2，字段3……）values(值1，值2，值3……)
查看数据select
命令：Select 字段1，字段2，……from 表名。字段之间用“,”隔开，如果查询所有的字段用“*”来代替。
修改数据update
命令：update 表名 set 字段名1=‘新值1’，字段名2=‘新值2’ where='条件' 如果不加where条件那么将会把所有的记录的值都修改掉。
删除数据delete
命令：delete from 表名 where 条件。如果不加where条件那么将会把所有的记录都删除掉。
mysql中的运算符
算术运算符：
大于： >
小于： <
等于： =
不等于： !=
大于等于： >=
小于等于： <=
逻辑运算符：
且： and
或者： or
between.....and.....
Between.....and.....：代表在什么什么之间，通常用于范围的查询
in查询
Delete from 学生表 where id in=10 orid=12 or id=13）
例子 Delete from 学生表 where id in（10,12,13）
排序的order by
Order by为排序的意思，格式为：order by 排序列 desc（指的是降序） asc（指的是升序） 默认为升序
例子：Select * from table order by id desc
模糊查询
模糊查询通常用于关键字查询，使用like来代表
% 代表任意多个字符 _ 代表任意一个字符
例如：查询出姓张的学生姓名
Select * from 学生表 where name like ‘张%’
查询出学生姓名中包含’大‘的学生信息
Select * from 学生表 where name like’%张%
查询出电话号的第二位是5的学生信息
elect * from 学生表 where name like’_5%’
limit限制记录条数（limit可以减轻mysql压力，主要用截取和分页）
偏移量 （分页时用的） ：（当前页减1）*每页条数
偏移量就是 limit 偏移量,每页条数
Limit用于限制结果集，限制查询出的条数，可以有一个参数也可以有两个参数，一个参数的时候代表从第一条数据查询取多少条，两个参数的时候，第一个参数代表从哪条记录开始，但不包括该条记录，第二个参数代表取多少条。
例如：查询出学生信息前三条
Select * from 学生表 limit 3
Select * from 学生表 limit 0，3
例如：按照学生年龄排序查询出年龄最大的学生，从第三条开始取5条（排序加截取）
Select * from 学生表 order by age desclimit 2，5
偏移量就是 limit 偏移量,每页条数
给表起别名 as
起别名用关键字 as
给表起别名：select * from 表名别名
给列起别名：select name as 用户名，sex as 性别 from 表名
关联查询
内连接查询数据
内连接又称全链接，用关键字inner join表示，内连接查询两张表中的数据，如果关联的条件相等，也就是两张表中同时存在的数据才会被查询出来
例如: Select * from 新闻表 inner join 分类表 on 新闻表.分类id=分类表.id
左外连接
左外连接用关键字left join 表示，以from后面的表为主表，去left join后面的表中匹配数据，如果匹配的不到则显示null
例如: Select * from 新闻表 left join 分类表 on 新闻表.分类id=分类表.id
右外连接
右外连接用关键字right join 表示，以from后面的表为主表，去right join后面的表中匹配数据，如果匹配的不到则显示null
例如: Select * from 新闻表 right join 分类表 on 新闻表.分类id=分类表.id
注意：where什么时候用什么时候不用
(inner join连接)(where条件)(like模糊查询)(limit限制条数)(desc排序)
例子：
Select * from table where id=1
Select * from table where id in (n....) like %%
Select * from table right join ... on...=... where id=1
Select * from table limit 1
Select * from table limit 30 desc id
Select * from table where id=1
select * from `res_students`
inner join `res_class` on `res_class`.`class_id`= `res_students`.`class_id` inner join `res_grade` on `res_sgrade`.`sid` =`res_students`.`sid`
where `res_students`.`class_id`= 1 and `g_add_date` = 2016-08-07
order by `res_grade`.`sid` asc
表名.键名 这样可以防止表里的键名相同导致报错
表名和键名注意关键字 键盘上esc按键下（横向数字1左边的按键）面的符号
` 可以防止关键字
php *** 作mysql的步骤
Php *** 作mysql必须让php打开mysql扩展：
打开php.ini文件，找到：extension=php_mysql.dll将前面的分号去掉，重启apache即可让php支持mysql扩展
编写程序调试php是否真正能够连接mysql：
① 连接数据库：mysql_connect(“数据库地址”,’管理员名称’,’管理员密码’)
② 选择数据库：mysql_select_db(“库名”,第一步返回的连接标识)
③ 设置字符集：mysql_query(‘set names utf8’)
④ 拼写sql语句执行，mysql_query(‘增删改查都可以’)
Php *** 作mysql常用函数：
mysql_connect（）：打开一个数据库连接，三个参数，第一个参数是数据库的地址，第二个参数是管理员名，第三个参数是密码 返回一个数据库连接标识
mysql_select_db（）：选择数据库，有两个参数 第一个参数是数据库名称，第二个参数，mysql_connect返回的数据库标识
mysql_query（）：执行sql语句 对select返回的是资源 对于其它类型的 SQL 语句，mysql_query() 在执行成功时返回 TRUE，出错时返回 FALSE。
mysql_fetch_array（）：从结果集中取得一行作为关联数组，或数字数组，或二者兼有
mysql_fetch_array（$res,MYSQL_NUM）这将是索引数组
mysql_fetch_array（$res,MYSQL_ASSOC）这将是关联数组
mysql_fetch_assoc（）：从结果集中取得一行作为关联数组
mysql_fetch_row（）：从结果集中取得一行作为索引数组
mysql_insert_id（）：取得刚刚插入自动增长的id 不需要写任何参数
例子：
<?php
//注意 php版本要 小于等于 5.3
header("content-type:text/htmlcharset=utf-8")
$link=mysql_connect("127.0.0.1",'root','root') or die('连接数据库错误')
mysql_select_db("test",$link) or die('选择数据库错误')
//mysql_select_db 第二个参数可要可不要 就近原则
mysql_query("set names utf8")
$sql='select * from bumen'
$res=mysql_query($sql)
?>
<?php
//服务器端文件代码：
//header头前不能有输出
header("content-type:text/htmlcharset=utf-8")
//接受用户注册的信息
$name=$_POST['username']
$pwd=$_POST['pwd']
$sex=$_POST['sex']
$bumen=$_POST['bumen']
$jianjie=$_POST['jianjie']
//① 连接数据库 mysql_connect('数据库的ip','管理员名称','密码')返回一个数据库连接标识是个资源
$link=mysql_connect('127.0.0.1','root','root')or die("数据库连接失败")
//② 选择数据库 mysql_select_db('库名',数据库的连接标识)
mysql_select_db('tt',$link)
echo mysql_error()
die
//③ 设置数据库的字符集 mysql_query('set namesutf8')
mysql_query('set names utf8')
//④ 写sql语句进行执行，增、删、改、查都可以的 用mysql_query()来执行
//注意：mysql_query()对于select返回的是一个资源，其他的也就是delete、insert、update返回的都是布尔也就是真或者假
$sql="insert into yuangong (name,pwd,sex,bumen,jianjie)values('$name','$pwd','$sex','$bumen','$jianjie')"
if(mysql_query($sql)){
echo "添加成功！"
}else{
echo "添加失败！"
}
?>
数据库可视化 *** 作工具
navicat (window安装收费)
phpmyadmin（免费跨平台，不用安装 php）
#1、查看现在mysql自增id的配置
show variables like '%increment%'
#2、下面步长为2的增长
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| auto_increment_increment | 2 |
| auto_increment_offset | 1 |
| div_precision_increment | 4 |
| innodb_autoextend_increment | 8 |
| ndb_autoincrement_prefetch_sz | 32 |
+-------------------------------+-------+
5 rows in set (0.01 sec)
#3、解决
set auto_increment_increment = 1和set @@auto_increment_increment = 1
如果想永久性的改回为1,在my.cnf or my.ini中查找这个field,然后修改，然后restart mysql.
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）