简介 “on duplicate key” 是在执行插入操作时的一个选项，它指示在遇到已经存在的键值时要执行的操作。当在数据库中使用插入操作时，如果键值已经存在，会触发错误。使用“on duplicate key”选项可以定义在遇到这种情况时要执行的操作。 例如，在MySQL中，可以在INSERT语句中使用“ON DUPLICATE KEY UPDATE”语句来指定在遇到重复键时要执行的操作。这个选项后面通常跟着一个或多个列名和值对，表示在遇到重复键时更新这些列的值。 下面是一个示例：  INSERT INTO my_table (id, name) VALUES (1, 'John') ON DUPLICATE KEY UPDATE name = 'John'; 在这个示例中，如果id为1的记录已经存在于my_table表中，那么插入操作将会更新该记录的name列的值为’John’；如果该记录不存在，则正常插入新记录。 需要注意的是，具体的语法和行为可能会因不同的数据库系统而有所差异。  唠叨一下 在mysql中插入数据，如果碰到主键冲突或者唯一索引冲突的时候，程序中会抛出SQLIntegrityConstraintViolationException异常，如果只是单纯的由于主键冲突或者唯一索引冲突引起的异常的话，这个问题很容易解决，如果是主键冲突的话可以更新为其他主键，如果是唯一索引的话可以将唯一索引删除或者将引起唯一索引冲突的数据更新一下；但是还有另外一种情况是比较棘手的，那就是我插入一条数据的时候如果出现这样的冲突的时候，我不希望什么都不做，而是当出现这样插入失败的时候我将冲突的这条数据进行一些更新操作，出现了这样的诉求的时候，下面的这个关键字就可以粉墨登场啦：on duplicate key update，这个关键字最重要的作用就是当我们在插入数据出现主键或唯一索引重复的时候，可以执行它后面的一个更新操作，而不是什么都不做，直接返回异常，接下来我们就来详细的了解一下 on duplicate key update 这个mysql中关键字在开发中正确使用。  实现原理 尝试插入新数据到数据库表中，如果插入成功则直接返回插入的列。 如果由于主键或唯一索引出现重复而造成插入失败的话，则先数据库表中已存在的出现冲突的数据行加上共享锁，然后将该数据返回数据库服务器中。 数据库服务器在内存中对改行数据执行on duplicate key update 后的update语句。 然后对该行数据加上排它锁。 最后将内存中update后的数据写入改行，全部执行完毕 使用实例 mybatis中单个插入on duplicate key update的使用 @Insert({"<script>",             "insert into user set id = #{id}, mobile = #{mobile}, email = #{email}, name = #{name}, link_name = #{linkName}, create_user_id = #{createUserId} ",             " on duplicate key update create_user_id = #{createUserId} ",             "</script>"})     int insertTest(User user); mybatis中批量插入on duplicate key update 的使用 @Insert({"<script>",             "insert into user  (id , mobile, email, name, link_name, create_user_id)  ",             " values ",             "<foreach collection='list' index='index' item='item' separator=','> (#{item.id}, #{item.mobile}, #{item.email}, #{item.name}, #{item.linkName}, #{item.createUserId} )</foreach>",             " on duplicate key update create_user_id = values(create_user_id), mobile = values(mobile) ",             "</script>"})     int batchInsertTest(List<User> userList); 存在问题 高并发情况可能会出现死锁 由于主键或唯一索引出现重复的时候，在更新该调存在的数据的时候，会为该条数据加一个共享锁和一个排它锁，最后才会写入该条数据，如果并发比较高，两个线程几乎同时来操作这条数据的话，如果两个线程同时给一条数据加上共享锁和排它锁的话，就会出现死锁的情况。  使用总结 在使用 on duplicate key update 关键字的时候，一定要注意它操作后返回结果，它的返回结果和我们平常使用的insert是不同的，使用insert … on duplicate key update时，如果主键或唯一索引没有重复，成功插入了新增数据则返回1，如果主键或唯一索引已经重复，没有插入成功，但是 on duplicate key update 后的更新的字段的数据和 原数据不同，则表示更新成功，此时返回的结果是2， 如果 on duplicate key update 的更新数据和原始数据相同，则表示更新也没有成功，则返回0. 在mysql中，同样可以实现通过判断主键或唯一索引是否存在来执行不同操作的关键字还有两个，分别是：replace into 和 insert ignore into，这几种每一种方式都会存在一些问题，具体存在哪些问题，就等着各位去进一步的深入探究啦 ————————————————                      原文链接：https://blog.csdn.net/zhangzehai2234/article/details/106309629 

最近在进行TiDB数据库操作时，遇到了一个问题，在使用insert into ... on duplicate key update操作时，系统返回了如下错误信息：  ERROR8141(HY000): assertion failed: key: xxxx, assertion: Exist, start_ts:xxxx, existing start ts:xxxx, existing commit ts:xx 本文将主要介绍这个问题的触发现象以及规避方法。  一、背景 在实际项目中，我使用了insert into ... on duplicate key update语句来处理数据插入或更新的逻辑。然而，当我执行这个操作时，TiDB会抛出异常报错，指向了一个8141的错误码。  //错误解读 首先，我通过官网去搜索该错误码8141的时候，发现官网有说明是因为索引数据不一致导致的，对于错误的详细解释是：  ERROR 8141(HY000): assertion failed: key: 7480000000000000455f6980000000000000020419b05820000000000419b0582000000000, assertion: Exist, start_ts:446128785517182978, existing start ts:446128785256087554, existing commit ts:446128785256087555 上述错误表明，事务提交时断言失败。根据数据索引一致的假设，TiDB 断言 key：  7480000000000000455f6980000000000000020419b05820000000000419b0582000000000不存在，提交事务时发现该 key 存在，是由 start ts 为 446128785256087554的事务写入的。TiDB 会将该 key 的 MVCC (Multi-Version Concurrency Control) 历史输出到日志。  二、原因排查 测试环境复现SQL如下：  #表结构 CREATETABLE`t`( `a`timestampNOTNULLONUPDATECURRENT_TIMESTAMP, `b`int(11) DEFAULTNULL, PRIMARY KEY(`a`) /*T![clustered_index] CLUSTERED */, KEY`idx`(`a`) ) ENGINE=InnoDBDEFAULTCHARSET=utf8mb4 COLLATE=utf8mb4_bin;   #插入语句类型insertinto.... onduplicatekeyupdate时候报错 insertintot values('2023-06-12 10:00:00',1) onduplicatekeyupdateb = values(b); insertintot values('2023-06-12 10:00:00',2) onduplicatekeyupdateb = values(b); insertintot values('2023-06-12 10:00:00',3) onduplicatekeyupdateb = values(b); 在这里可以看到，当我们执行到第三条INSERT时候发现，会发生报错： 最开始比较费解的是，为什么仅仅插入几条数据，就会出现索引不一致的情况，索引数据不一致情况我把他分为两类，一种是数量不匹配，另一种是数值不匹配。  我先是通过使用主键和使用索引两种方式来观察数据。既然是索引数据不一致，那我们只需要观察a列就好了，SQL如下：  select/*+ use_index(t,primary)*/a fromt useindex(primary); select/*+ use_index(t,idx)*/a fromt useindex(idx);  这个时候我们可以看到，索引里面a列的数据为：2023-12-06 15:05:17 ，而主键里面a列的数据为：2023-06-12 10:00:00。 那么这两个数值为什么会造成这种差异呢？我观察了一下发现，我设置的a列是一个时间字段，并且呢，他是一个自动更新的时间字段ON UPDATE CURRENT_TIMESTAMP，也就是说，如果发生该行变更的情况下，a列的值应该更新为最新时间才对，目前从两者差异来看的话，可以看出，该值在主键里并没有更新，反而是在索引里面更新了，也就是说，其实现在索引里面的数据是真实的，主键里面的数据是不匹配的。  那可以知道大概率是因为ON UPDATE CURRENT_TIMESTAMP列引起的，我就去查了一下看是否有相关issues，找到相关issues：  https://github.com/pingcap/tidb/issues/44565  三、问题分析 找到问题后，进行多次测试，发现该问题总共会有两种现象，下面进行分开说明一下。  //现象一 触发条件存在的情况下，当我们进行update操作触发 aotu update 更新字段时，会造成索引数据进行更新，但是聚簇主键的字段没有进行更新，导致数据与索引不一致情况发生，该现象与我们在原因排查里面看到的现象一致。  该现象的情况发生在隐式事务提交下，因为我们知道隐式事务提交使用的是乐观事务，在乐观事务下，所进行的assertion检查项要跟悲观事务相比存在差异，所以在提交的时候会报错，导致提交失败，这种情况下只会导致主键和索引的数值不一样，主键和索引的数量是一样的。  //现象二 现象二则不同，该现象是在开启显示事务提交下触发，显示事务提交是使用的悲观事务进行，这样会导致批量插入最后进行commit时候不报错。 复现SQL如下：  CREATETABLE`t`( `a`timestampNOTNULLONUPDATECURRENT_TIMESTAMP, `b`int(11) DEFAULTNULL, PRIMARY KEY(`a`) /*T![clustered_index] CLUSTERED */, KEY`idx`(`a`) ) ENGINE=InnoDBDEFAULTCHARSET=utf8mb4 COLLATE=utf8mb4_bin;   begin; insertintot values('2023-06-11 10:00:00',1) onduplicatekeyupdateb = values(b); insertintot values('2023-06-11 10:00:00',2) onduplicatekeyupdateb = values(b); insertintot values('2023-06-11 10:00:00',3) onduplicatekeyupdateb = values(b); commit;   begin; insertintot values('2023-06-12 10:00:00',1) onduplicatekeyupdateb = values(b); insertintot values('2023-06-12 10:00:00',2) onduplicatekeyupdateb = values(b); insertintot values('2023-06-12 10:00:00',3) onduplicatekeyupdateb = values(b); commit;   admin checktablet; select/*+ use_index(t,primary)*/a fromt useindex(primary); select/*+ use_index(t,idx)*/a fromt useindex(idx); 可以看到，在事务提交时候是没有进行报错提示的，只有在我们进行check时候才返回报错。 如果想要不发生这种情况，需要将assertion级别调高，可以通过系统变量tidb_txn_assertion_level 进行调整。 该变量在v6.0版本引入，默认为FAST，调到最高级别STRICT则可以在commit阶段提示报错。  需要注意的是，这个变量因为是v6.0引入的，如果有低版本集群是原地升级升上来的，那么这个值默认是OFF，也就是关闭状态，这就导致在关闭状态下，这样进行插入操作就不会出现索引数据不一致报错，而是一直插入，导致无法发现问题。  //触发条件说明 根据自己测试来看，触发条件总共有三条，必须三条同时满足，才会触发该BUG，分别是：  有 ON UPDATE CURRENT_TIMESTAMP 列 该列同时在clustered index 中 执行SQL时候，没有显示指定该列，而是去触发auto update 四、问题总结 综上所述，该问题的发生主要是因为聚簇主键列如果存在auto update约束时，不会触发自动更新导致，该问题于v6.5.3版本进行修复。如果有满足触发条件的表，提供如下几点建议：  1、如果是UPDATE语句，在UPDATE当中手动去指定含有ON UPDATE CURRENT_TIMESTAMP 条件字段的值，避免让主键进行auto update操作。 2、如果是insert into ... on duplicate key update 语句，建议更改为replaice into 方式，遇到重复值进行delete + insert 操作，避免使用update。 ————————————————                       原文链接：https://blog.csdn.net/CBGCampus/article/details/135815040 

在实际应用中，经常碰到导入数据的功能，当导入的数据不存在时则进行添加，有修改时则进行更新， 　　在刚碰到的时候，一般思路是将其实现分为两块，分别是判断增加，判断更新，后来发现在mysql中有ON DUPLICATE KEY UPDATE一步就可以完成(Mysql独有的语法)。  ON DUPLICATE KEY UPDATE单个增加更新及批量增加更新的sql 在MySQL数据库中，如果在insert语句后面带上ON DUPLICATE KEY UPDATE 子句，而要插入的行与表中现有记录的惟一索引或主键中产生重复值，那么就会发生旧行的更新；如果插入的行数据与现有表中记录的唯一索引或者主键不重复，则执行新纪录插入操作。  说通俗点就是数据库中存在某个记录时，执行这个语句会更新，而不存在这条记录时，就会插入。 注意点： 　　因为这是个插入语句，所以不能加where条件。 　　如果是插入操作，受到影响行的值为1；如果更新操作，受到影响行的值为2；如果更新的数据和已有的数据一样（就相当于没变，所有值保持不变），受到影响的行的值为0。  该语句是基于唯一索引或主键使用，比如一个字段a被加上了unique index，并且表中已经存在了一条记录值为1，  下面两个语句会有相同的效果：  INSERT INTO table (a,b,c) VALUES (1,2,3)     ON DUPLICATE KEY UPDATE c=c+1;      UPDATE table SET c=c+1 WHERE a=1; ON DUPLICATE KEY UPDATE后面可以放多个字段，用英文逗号分割。  再现一个例子：  INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)         ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);  表中将更改（增加或修改）两条记录。  在mybatis中进行单个增加或修改sql的写法为：  <insert id="insertOrUpdateCameraInfoByOne" paramerType="com.pojo.AreaInfo">     insert into camera_info( cameraId,zone1Id,zone1Name,zone2Id,zone2Name,zone3Id,zone3Name,zone4Id,zone4Name）     VALUES(         #{cameraId},#{zone1Id},#{zone1Name}, #{zone2Id},         #{zone2Name}, #{zone3Id}, #{zone3Name},         #{zone4Id}, #{zone4Name},)     ON DUPLICATE KEY UPDATE      cameraId = VALUES(cameraId),     zone1Id = VALUES(zone1Id),zone1Name = VALUES(zone1Name),     zone2Id = VALUES(zone2Id),zone2Name = VALUES(zone2Name),     zone3Id = VALUES(zone3Id),zone3Name = VALUES(zone3Name),     zone4Id = VALUES(zone4Id),zone4Name = VALUES(zone4Name) </insert> 在mybatis中进行批量增加或修改的sql为：  <insert id="insertOrUpdateCameraInfoByBatch" parameterType="java.util.List">       insert into camera_info(           zone1Id,zone1Name,zone2Id,zone2Name,zone3Id,zone3Name,zone4Id,zone4Name,           cameraId           )VALUES            <foreach collection ="list" item="cameraInfo" index= "index" separator =",">              (                 #{cameraInfo.zone1Id}, #{cameraInfo.zone1Name}, #{cameraInfo.zone2Id},                 #{cameraInfo.zone2Name}, #{cameraInfo.zone3Id}, #{cameraInfo.zone3Name},                 #{cameraInfo.zone4Id}, #{cameraInfo.zone4Name},                  #{cameraInfo.cameraId},               )            </foreach>            ON DUPLICATE KEY UPDATE                zone1Id = VALUES(zone1Id),zone1Name = VALUES(zone1Name),zone2Id = VALUES(zone2Id),                zone2Name = VALUES(zone2Name),zone3Id = VALUES(zone3Id),zone3Name = VALUES(zone3Name),                zone4Id = VALUES(zone4Id),zone4Name = VALUES(zone4Name),                cameraId = VALUES(cameraId)     </insert> 项目中数据的操作有时候会令人头大，遇到一个需求： 需要将数据从A数据库的a数据表同步到B数据库的b数据表中（ab表结构相同，但不是主从关系。。。just同步过去） 第一次同步过去，b表为空，同步很简单。 但是当a表中的某些数据更新且增加了新数据之后，再想让两个表同步就有些麻烦了。（如果把b表清空，重新同步，数据量过大的话耗费的时间太长，不是一个好办法）  想着能不能按照时间段来做更新，这段时间内有新数据了，就插入数据，有数据更新了就更新数据。先说下我的思路：  步骤： 　　1.首先我从a表取出某一时间段的数据（分段更新） 　　2.往b表内放数据，根据主键判断b表是否已经有此条记录，没有此数据则插入，有了记录则对比数据是否一样，一样则不做更改，不一样就做更新操作。  此时使用该语句可以满足需要，但是要注意几个问题：  更新的内容中unique key或者primary key最好保证一个，不然不能保证语句执行正确(有任意一个unique key重复就会走更新,当然如果更新的语句中在表中也有重复校验的字段，那么也不会更新成功而导致报错,只有当该条语句没有任何一个unique key重复才会插入新记录)；尽量不对存在多个唯一键的table使用该语句，避免可能导致数据错乱。  在有可能有并发事务执行的insert 语句情况下不使用该语句，可能导致产生death lock。 如果数据表id是自动递增的不建议使用该语句；id不连续，如果前面更新的比较多，新增的下一条会相应跳跃的更大。 该语句是mysql独有的语法，如果可能会设计到其他数据库语言跨库要谨慎使用。 主键不连续自增解决方法 源引自：https://www.linuxidc.com/Linux/2018-01/150427.htm  最近项目上需要实现这么一个功能:统计每个人每个软件的使用时长,客户端发过来消息,如果该用户该软件已经存在增更新使用时间,如果没有则新添加一条记录,代码如下:      <!-- 批量保存软件使用时长表 -->     <update  id="saveApp"  parameterType="java.util.List">     <foreach collection="appList" item="item" index="index"  separator=";">      insert into app_table(userName,app,duration)      values(#{userName},#{item.app},#{item.duration})      on duplicate key update duration=duration+#{item.duration}   </foreach>   </update>  为了效率用到了on duplicate key update进行自动判断是更新还是新增,一段时间后发现该表的主键id(已设置为连续自增),不是连续的自增,总是跳跃的增加,这样就造成id自增过快,已经快超过最大值了,通过查找资料发现,on duplicate key update有一个特性就是,每次是更新的情况下id也是会自增加1的,比如说现在id最大值的5,然后进行了一次更新操作,再进行一次插入操作时,id的值就变成了7而不是6. 为了解决这个问题,有两种方式,第一种是修改innodb_autoinc_lock_mode中的模式,第二种是将语句修拆分为更新和操作2个动作  第一种方式:innodb_autoinc_lock_mode中有3中模式,0,1和2,mysql5的默认配置是1, 0是每次分配自增id的时候都会锁表. 1只有在bulk insert的时候才会锁表,简单insert的时候只会使用一个light-weight mutex,比0的并发性能高 2.没有仔细看,好像是很多的不保证...不太安全.  数据库默认是1的情况下,就会发生上面的那种现象,每次使用insert into .. on duplicate key update 的时候都会把简单自增id增加,不管是发生了insert还是update  由于该代码数据量大,同时需要更新和添加的数据量多,不能使用将0模式,只能将数据库代码拆分成为更新和插入2个步骤,第一步先根据用户名和软件名更新使用时长,代码如下:    <update id="updateApp" parameterType="App">    update app_table    set duration=duration+#{duration}    where userName=#{userName} and appName=#{appName}   </update>  然后根据返回值,如果返回值大于0,说明更新成功不再需要插入数据,如果返回值小于0则需要进行插入该条数据,代码如下:  <insert id="saveApp" keyProperty = "id" useGeneratedKeys = "true"  parameterType="App">    insert into app_table(userName,appName,duration)    values(#{userName},#{appName},#{duration})   </insert>  产生death lock原理 insert ... on duplicate key 在执行时，innodb引擎会先判断插入的行是否产生重复key错误，如果存在，在对该现有的行加上S（共享锁）锁，如果返回该行数据给mysql,然后mysql执行完duplicate后的update操作，然后对该记录加上X（排他锁），最后进行update写入。  如果有两个事务并发的执行同样的语句，那么就会产生death lock ———————————————— 原文链接：https://blog.csdn.net/wys0127/article/details/132078949 

一：主键索引，唯一索引和普通索引的关系 主键索引 主键索引是唯一索引的特殊类型。  数据库表通常有一列或列组合，其值用来唯一标识表中的每一行。该列称为表的主键。  在数据库关系图中为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的。当在查询中使用主键索引时，它还允许快速访问数据。主键索引不能为空。每个表只能有一个主键  唯一索引： 不允许两行具有相同的索引值。但可以都为NULL，笔者亲试。  如果现有数据中存在重复的键值，则数据库不允许将新创建的唯一索引与表一起保存。当新数据将使表中的键值重复时，数据库也拒绝接受此数据。每个表可以有多个唯一索引 普通索引： 一般的索引结构，可以在条件删选时加快查询效率，索引字段的值可以重复，可以为空值  二：ON DUPLICATE KEY UPDATE使用测试(MYSQL下的Innodb引擎) 上面介绍了索引的知识，是为了介绍这个ON DUPLICATE KEY UPDATE功能做铺垫。  1：ON DUPLICATE KEY UPDATE功能介绍： 有时候由于业务需求，可能需要先去根据某一字段值查询数据库中是否有记录，有则更新，没有则插入。你可能是下面这样写的  if not exists (select node_name from node_status where node_name = target_name)       insert into node_status(node_name,ip,...) values('target_name','ip',...) else       update node_status set ip = 'ip',site = 'site',... where node_name = target_name 这样写在大多数情况下可以满足我们的需求，但是会有两个问题。  ①性能带来开销，尤其是系统比较大的时候。 ②在高并发的情况下会出现错误，可能需要利用事务保证安全。 有没有一种优雅的写法来实现有则更新，没有则插入的写法呢？ON DUPLICATE KEY UPDATE提供了这样的一个方式。 2：ON DUPLICATE KEY UPDATE测试样例+总结： 首先我们了解下这个简单的表结构id(主键)、code、name。  看下表中现有的数据：  执行以下实验进行分析： 实验一：含有ON DUPLICATE KEY UPDATE的INSERT语句中包含主键： ①插入更新都失败，原因是因为把主键id改成了已经存在的id  ②执行更新操作。这里的数据还是四条。不过第四条的id由75变化为85  ③执行更新操作。数据总量是四条  ④insert语句中未包含主键，执行插入操作。数据量变为5条 实验二：含有ON DUPLICATE KEY UPDATE的INSERT语句中包含唯一索引： 表结构中增加code的唯一索引，表中现有的数据：  ①插入更新都失败，原因是因为把code改成了已经存在的code值  ②执行更新操作。这里的数据总量为5条。不过第五条的code由1000变化为1200  ③执行更新操作。数据总量五条，没有变化  ④insert语句中未包含唯一索引，执行插入操作。数据量变为6条 总结：  1：ON DUPLICATE KEY UPDATE需要有在INSERT语句中有存在主键或者唯一索引的列，并且对应的数据已经在表中才会执行更新操作。而且如果要更新的字段是主键或者唯一索引，不能和表中已有的数据重复，否则插入更新都失败。  2：不管是更新还是增加语句都不允许将主键或者唯一索引的对应字段的数据变成表中已经存在的数据。 ————————————————                       原文链接：https://blog.csdn.net/lovedingd/article/details/123508783 

文章目录 一、ON DUPLICATE KEY UPDATE的介绍 二、ON DUPLICATE KEY UPDATE的使用 2.1、案例一：根据主键id进行更新 2.2、案例二：根据唯一索引进行更新（常用） 2.3、案例三：没有主键或唯一键字段值相同就插入 2.4、案例四：主键与唯一键字段同时存在 三、ON DUPLICATE KEY UPDATE的注意事项 3.1、on dupdate key update之后values的使用事项 3.2、对values使用判断 3.3、唯一索引大小写敏感问题 四、ON DUPLICATE KEY UPDATE与mybatis联合使用 4.1、写法一：与values()联合使用 4.2、写法二：使用#{} 五、ON DUPLICATE KEY UPDATE的缺点及坑 5.1、ON DUPLICATE KEY UPDATE每次更新导致id不连续 5.2、death lock死锁 有时候由于业务需求，可能需要先去根据某一字段值查询数据库中是否有记录，有则更新，没有则插入。这个时候就可以用到ON DUPLICATE KEY UPDATE这个sql语句了。  以下内容基于本地windows环境mysql:8.0.34进行讲解。  一、ON DUPLICATE KEY UPDATE的介绍 基本用法：ON DUPLICATE KEY UPDATE​​是一种MySQL的语法，它在插入新数据时，如果遇到唯一键冲突（即已存在相同的唯一键值），则会执行更新操作，而不是抛出异常或忽略该条数据。这个语法可以大大简化我们的代码，减少不必要的判断和查询操作。  用法总结 1：on duplicate key update 语句根据主键id或唯一键来判断当前插入是否已存在。 2：记录已存在时，只会更新on duplicate key update之后指定的字段。 3：如果同时传递了主键和唯一键，以主键为判断存在依据，唯一键字段内容可以被修改。 4：唯一键大小写敏感时，大小写不同的值被认为是两个值，执行插入。参见下文中的大小写敏感问题  二、ON DUPLICATE KEY UPDATE的使用 准备表结构及测试数据， 注意：name是唯一键  drop table if exists tbl_test; create table tbl_test(     id int primary key auto_increment,     name varchar(30) unique not null,     age int comment '年龄',     address varchar(50) comment '住址',     update_time datetime default null ) comment '测试表';  insert into tbl_test(name,age,address,update_time) values('zhangsan',20,'杭州',now()),('lisi',21,'武汉',now()); 测试数据如下： 2.1、案例一：根据主键id进行更新 on dupdate key update 语句基本功能是：当表中没有原来记录时，就插入，有的话就更新。  如下sql：  insert into tbl_test(id,name,age,address,update_time) values(1,'zhangsan1',201,'杭州1','2024-03-05 15:59:35') on duplicate key update age = values(age), -- 注意：values()括号中的内容是字段名称。比如：在java程序中使用时表字段可能叫user_name, 实体类中是userName，values()里面要填user_name address = values(address), update_time=now(); 从执行结果可以看出，更新了id为1的age，address两个字段，而name字段没有修改生效。由此我们可以得出两个重要结论： 1：on duplicate key update 语句根据主键id来判断当前插入是否已存在。 2：已存在时，只会更新on duplicate key update之后限定的字段。  2.2、案例二：根据唯一索引进行更新（常用） 根据唯一索引进行更新是生产中比较常用的方式，因为id一般使用的是自增，很少会先把id查询出来，然后根据id进行更新。  如下sql：  insert into tbl_test(name,age,address) values('zhangsan',202,'杭州2') on duplicate key update age = values(age), -- 注意：values()括号中的内容是字段名称。比如：在java程序中使用时表字段可能叫user_name, 实体类中是userName，values()里面要填user_name address = values(address), update_time=now(); 从执行结果看，这次没有传id，但是age,address字段仍然更新了。 由此可以得出另一个结论： 3：on duplicate key update 语句也可以根据唯一键来判断当前插入的记录是否已存在。  2.3、案例三：没有主键或唯一键字段值相同就插入 如下sql：  insert into tbl_test(name,age,address) values('zhangsan3',203,'杭州3') on duplicate key update age = values(age), -- 注意：values()括号中的内容是字段名称。比如：在java程序中使用时表字段可能叫user_name, 实体类中是userName，values()里面要填user_name address = values(address), update_time=now(); 这条执行就比较简单了，没有主键或唯一键字段值相同，即判断当前记录不存在，新插入一条。  注意： 这里我们发现主键id并没有连续，直接从2变成了4，具体原理可见《MySQL数据库设置主键自增、自增主键为什么不能保证连续递增》  2.4、案例四：主键与唯一键字段同时存在 如下sql:  insert into tbl_test(id,name,age,address) values(1,'zhangsan4',204,'杭州4') on duplicate key update name = values(name), age = values(age), -- 注意：values()括号中的内容是字段名称。比如：在java程序中使用时表字段可能叫user_name, 实体类中是userName，values()里面要填user_name address = values(address), update_time=now();  从上面可以看出，连唯一键name也被修改了。结论： 4：如果传递了主键，是可以修改唯一键字段内容的。 这里要注意，如果这里的name修改为 lisi,zhangsan3 会报唯一键冲突的。可以自行尝试。  三、ON DUPLICATE KEY UPDATE的注意事项 3.1、on dupdate key update之后values的使用事项 如下sql:  insert into tbl_test(name,age,address,update_time) values('zhangsan4',205,'杭州5','2024-03-05 00:00:00') on duplicate key update age = age, address = '杭州', update_time=values(update_time); on dupdate key update之后没有用values的情况 分为两种情况： 1：如果为如上面的address= “杭州”，则会一直更新为"杭州". 2：如果为如上面的age = age，则age会保持数据库中的值，不会更新。 3：只有当使用了values后，才会更新为上下文中传入的值  3.2、对values使用判断 如下sql  insert into tbl_test(id,name,age,address) values(1,'zhangsan',202,'杭州2') on duplicate key update name = ifnull(values(name),name), age = values(age) 达到的效果是，如果传入的name值为null，则不更新。不为null则更新。这里与mybatis配合使用比较好。  3.3、唯一索引大小写敏感问题 思考这么一个问题：如上面name作为唯一索引，当name大小写敏感时且数据库中存储了name=“zhangsan” ，那么再插入name="ZHANGSAN"是更新还是新增？  1）：唯一索引大小写不敏感时 设置name字段为唯一索引且大小写不敏感  drop table if exists tbl_test; create table tbl_test(     id int primary key auto_increment,     name varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci unique not null,     age int comment '年龄',     address varchar(50) comment '住址',     update_time datetime default null ) comment '测试表';  insert into tbl_test(name,age,address,update_time) values('zhangsan',20,'杭州',now()); insert into tbl_test(name,age,address,update_time) values('ZHANGSAN',21,'杭州1',now());  可以看到当字段为大小写不敏感时zhangsan跟ZHANGSAN被认为是同一个值，不能重复插入。  当数据库中name=zhangsan时且name字段大小写不敏感时，我们看一下name="ZHANGSAN"能否更新成功？  insert into tbl_test(name,age,address,update_time) values('ZHANGSAN',22,'杭州2','2024-03-05 00:00:00') on duplicate key update age = values(age), address = values(address), update_time=values(update_time);  以上结果可以看出，当大小写不敏感时on duplicate key update是可以更新成功的，即认为是同一个值。  2）：唯一索引大小写敏感时 设置name字段为唯一索引且大小写敏感  drop table if exists tbl_test; create table tbl_test(     id int primary key auto_increment,     name varchar(30) CHARACTER SET utf8 COLLATE utf8_bin unique not null,     age int comment '年龄',     address varchar(50) comment '住址',     update_time datetime default null ) comment '测试表';  insert into tbl_test(name,age,address,update_time) values('zhangsan',20,'杭州',now()); insert into tbl_test(name,age,address,update_time) values('ZHANGSAN',21,'杭州1',now());  可以看到当字段为大小写敏感时zhangsan跟ZHANGSAN被认为是两个值，插入了两条记录。所以此时用on duplicate key update会执行新增操作  四、ON DUPLICATE KEY UPDATE与mybatis联合使用 4.1、写法一：与values()联合使用 注意：values后面的内容是表字段名称即带下划线，而不是实体类驼峰名称  如下sql: dept_id为主键或唯一索引  <insert id="replaceInto">     INSERT INTO sys_dept(         dept_id,         parent_id,         status,         update_time)      VALUES     <foreach collection="deptList" item="item" separator=",">         (#{item.deptId},         #{item.parentId},         #{item.status},         #{item.updateTime})     </foreach>     ON DUPLICATE KEY UPDATE         parent_id=VALUES(parent_id),         status=VALUES(status),         update_time=VALUES(update_time) </insert> 4.2、写法二：使用#{} 如下sql: dept_id为主键或唯一索引  <insert id="replaceInto">     INSERT INTO sys_dept(         dept_id,         parent_id,         status,         update_time)      VALUES     <foreach collection="deptList" item="item" separator=",">         (#{item.deptId},         #{item.parentId},         #{item.status},         #{item.updateTime})     </foreach>     ON DUPLICATE KEY UPDATE     <foreach collection="deptList" item="item" separator=",">            parent_id =  #{item.parentId},            status = #{item.status},            update_time = #{item.updateTime}     </foreach> </insert> 五、ON DUPLICATE KEY UPDATE的缺点及坑 5.1、ON DUPLICATE KEY UPDATE每次更新导致id不连续 如下sql：  drop table if exists tbl_test; create table tbl_test(     id int primary key auto_increment,     name varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci unique not null,     age int comment '年龄',     address varchar(50) comment '住址',     update_time datetime default null ) comment '测试表';  insert into tbl_test(name,age,address,update_time) values('zhangsan',20,'杭州',now()),('李四',21,'武汉',now());  执行on duplicate key update进行更新，然后再插入一条新的数据 insert into tbl_test(name,age,address,update_time) values('zhangsan',22,'杭州2','2024-03-05 00:00:00') on duplicate key update age = values(age), address = values(address), update_time=values(update_time); insert into tbl_test(name,age,address,update_time) values('王五',23,'深圳',now()); 可以看到id自增值从2直接变成了4，造成了id的不连续。  1.ON DUPLICATE KEY UPDATE每次更新导致id不连续原理： mysql中有个配置值是innodb_autoinc_lock_mode。 innodb_autoinc_lock_mode中有3中模式,0,1和2,mysql5的默认配置是1,  0是每次分配自增id的时候都会锁表. 1只有在bulk insert的时候才会锁表,简单insert的时候只会使用一个light-weight mutex,比0的并发性能高 2.没有仔细看,好像是很多的不保证…不太安全. 数据库默认是1的情况下,就会发生上面的那种现象,每次使用insert into … on duplicate key update 的时候都会把简单自增id增加,不管是发生了insert还是update  5.2、death lock死锁 经常看到网上说ON DUPLICATE KEY UPDATE会导致死锁，确实是存在这个可能的，不过由于目前没有特别好的方案，所以也只能使用这个sql语法了。在执行insert ... on duplicate key语句时，如果不对同一个表同时进行并发的insert或者update，基本不会造成死锁。即insert ... on duplicate key时尽量单线程串行进行新增或更新  insert … on duplicate key 在执行时，innodb引擎会先判断插入的行是否产生重复key错误，如果存在，在对该现有的行加上S（共享锁）锁，如果返回该行数据给mysql,然后mysql执行完duplicate后的update操作，然后对该记录加上X（排他锁），最后进行update写入。  如果有两个事务并发的执行同样的语句，那么就会产生death lock ————————————————             原文链接：https://blog.csdn.net/weixin_49114503/article/details/136479860 

注意：ON DUPLICATE KEY UPDATE 是Mysql特有的语法，仅Mysql有效。 作用：当执行insert操作时，有已经存在的记录，执行update操作。 用法： 有一个test表，id为主键。第一次插入数据 INSERT INTO test(id,name,age)VALUES(1,'2',3),(11,'22',33) 此时表中数据增加了一条主键’id’为‘1’和‘11’的两条记录，当我们再次执行一条id为1的插入语句时，会发生什么呢？ INSERT INTO test(id,name,age)VALUES(1,'张三',13) INSERT INTO test(id,name,age)VALUES(1,'张三',13) > 1062 - Duplicate entry '1' for key 'PRIMARY' > 时间: 0.034s   Mysql告诉我们，我们的主键冲突了，看到这里我们是不是可以改变一下思路，当插入已存在主键的记录时，将插入操作变为修改： -- 在原sql后面增加 ON DUPLICATE KEY UPDATE INSERT INTO test ( id, NAME, age ) VALUES( 1, '张三', 13 )      ON DUPLICATE KEY UPDATE id = 1,     NAME = '张三',     age = 13  执行结果中受影响的行数是2。 -- 在原sql后面增加 ON DUPLICATE KEY UPDATE INSERT INTO test ( id, NAME, age ) VALUES( 1, '张三', 13 )      ON DUPLICATE KEY UPDATE id = 1,     NAME = '张三',     age = 13 > Affected rows: 2 > 时间: 0.18s 执行上面的语句结果  此时我们如果再次插入( 1, '张三', 13 ) 的数据时会有什么结果 -- 在原sql后面增加 ON DUPLICATE KEY UPDATE INSERT INTO test ( id, NAME, age ) VALUES( 1, '张三', 13 )      ON DUPLICATE KEY UPDATE id = 1,     NAME = '张三',     age = 13 > Affected rows: 0 > 时间: 0.013s 可以看到影响的行数为0。插入的时候主键冲突，ON DUPLICATE KEY UPDATE会执行更新操作，更新为id = 1,NAME = '张三',age = 13 ，但是并没有我们想象的执行更新。 总结：ON DUPLICATE KEY UPDATE首先会检查插入的数据主键是否冲突，如果冲突则执行更新操作，如果ON DUPLICATE KEY UPDATE的子句中要更新的值与原来的值都一样，则不更新。如果有一个值与原值不一样，则更新： -- 在原sql后面增加 ON DUPLICATE KEY UPDATE INSERT INTO test ( id, NAME, age ) VALUES( 1, '张三', 13 )      ON DUPLICATE KEY UPDATE id = 1,     NAME = '张三',     age = 133 > Affected rows: 2 > 时间: 0.014s  执行完毕，id为1的age值改为133 目前id为1的数据age字段值为13，我们执行插入语句时只改变了其中一个值age=133,则影响行数为2。此时注意VALUES( 1, '张三', 13 )  中age值为13，ON DUPLICATE KEY UPDATE子句中age值为133。 如果插入的数据主键有冲突，则修改字段值以ON DUPLICATE KEY UPDATE子句的值为准。 ON DUPLICATE KEY UPDATE子句写的是固定值，怎么动态赋值呢？如果一次插入多条数据，怎么动态获取主键冲突所要更新的值呢？ ON DUPLICATE KEY UPDATE age = VALUES(age)  总结： 1. ON DUPLICATE KEY UPDATE检查主键或唯一索引字段是否冲突。 2. update的字段值与现存的字段值相同，则不更新。 3. 动态更新字段值用VALUES(字段名称)。 ————————————————               原文链接：https://blog.csdn.net/qq_38803590/article/details/124692041 

前言mysql5.7版本开始支持JSON类型字段，本文详细介绍json_extract函数如何获取mysql中的JSON类型数据json_extract可以完全简写为 ->json_unquote(json_extract())可以完全简写为 ->>下面介绍中大部分会利用简写创建示例表12345CREATE TABLE `test_json` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `content` json DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;123# 插入两条测试用的记录INSERT INTO `test_json` (`content`) VALUES ('{\"name\":\"tom\",\"age\":18,\"score\":[100,90,87],\"address\":{\"province\":\"湖南\",\"city\":\"长沙\"}}');INSERT INTO `test_json` (`content`) VALUES ('[1, "apple", "red", {"age": 18, "name": "tom"}]');idcontent1{“age”: 18, “name”: “tom”, “score”: [100, 90, 87], “address”: {“city”: “长沙”, “province”: “湖南”}}2[1, “apple”, “red”, {“age”: 18, “name”: “tom”}]基本语法获取JSON对象中某个key对应的value值json_extract函数中，第一个参数content表示json数据，第二个参数为json路径，其中 表示该 j s o n 数据本身， 表示该json数据本身， 表示该json数据本身，.name就表示获取json中key为name的value值可以利用 -> 表达式来代替json_extract若获取的val本身为字符串，那么获取的val会被引号包起来，比如"tom"，这种数据被解析到程序对象中时，可能会被转义为\“tom\”。为了解决这个问题了，可以在外面再包上一层json_unquote函数，或者使用 ->> 代替->12content:{“age”: 18, “name”: “tom”, “score”: [100, 90, 87], “address”: {“city”: “长沙”, “provi # 得到"tom" select json_extract(content,'$.name') from test_json where id = 1; # 简写方式：字段名->表达式等价于json_extract(字段名,表达式) select content->'$.name' from test_json where id = 1; # 结果: +--------------------------------+ | json_extract(content,'$.name') | +--------------------------------+ | "tom"                          | +--------------------------------+ +-------------------+ | content->'$.name' | +-------------------+ | "tom"             | +-------------------+  # 解除双引号，得到tom select json_unquote(json_extract(content,'$.name')) from test_json where id = 1; # 简写方式：字段名->>表达式等价于json_unquote(json_extract(字段名,表达式)) select content->>'$.name' from test_json where id = 1; # 结果: +----------------------------------------------+ | json_unquote(json_extract(content,'$.name')) | +----------------------------------------------+ | tom                                          | +----------------------------------------------+ +--------------------+ | content->>'$.name' | +--------------------+ | tom                | +--------------------+  获取JSON数组中某个元素json_extract函数中，第一个参数content表示json数据，第二个参数为json路径，其中 表示该 j s o n 数据本身， 表示该json数据本身， 表示该json数据本身，[i]表示获取该json数组索引为i的元素（索引从0开始）与获取key-val一样，若获取的元素为字符串，默认的方式也会得到双引号包起来的字符，导致程序转义，方法也是利用json_unquote函数，或者使用 ->> 代替->12content:[1, “apple”, “red”, {“age”: 18, “name”: “tom”}] # 得到"apple" select json_extract(content,'$[1]') from test_json where id = 2; # 简写，效果同上 select content->'$[1]' from test_json where id = 2; # 结果: +------------------------------+ | json_extract(content,'$[1]') | +------------------------------+ | "apple"                      | +------------------------------+ +-----------------+ | content->'$[1]' | +-----------------+ | "apple"         | +-----------------+  # 解除双引号，得到apple  select json_unquote(json_extract(content,'$[1]')) from test_json where id = 2; # 简写，效果同上 select content->>'$[1]' from test_json where id = 2; # 结果: +--------------------------------------------+ | json_unquote(json_extract(content,'$[1]')) | +--------------------------------------------+ | apple                                      | +--------------------------------------------+ +------------------+ | content->>'$[1]' | +------------------+ | apple            | +------------------+  获取JSON中的嵌套数据结合前面介绍的两种获取方式，可以获取json数据中的嵌套数据1234content: id=1{“age”: 18, “name”: “tom”, “score”: [100, 90, 87], “address”: {“city”: “长沙”, “province”: “湖南”}}content: id=2[1, “apple”, “red”, {“age”: 18, “name”: “tom”}] # 得到:87 select content->'$.score[2]' from test_json where id = 1; # 结果: +-----------------------+ | content->'$.score[2]' | +-----------------------+ | 87                    | +-----------------------+  # 得到:18 select content->'$[3].age' from test_json where id = 2; # 结果: +---------------------+ | content->'$[3].age' | +---------------------+ | 18                  | +---------------------+ 渐入佳境获取JSON多个路径的数据将会把多个路径的数据组合成数组返回12content: id=1{“age”: 18, “name”: “tom”, “score”: [100, 90, 87], “address”: {“city”: “长沙”, “province”: “ select json_extract(content,'$.age','$.score') from test_json where id = 1; # 结果: +-----------------------------------------+ | json_extract(content,'$.age','$.score') | +-----------------------------------------+ | [18, [100, 90, 87]]                     | +-----------------------------------------+  select json_extract(content,'$.name','$.address.province','$.address.city') from test_json where id = 1; # 结果: +----------------------------------------------------------------------+ | json_extract(content,'$.name','$.address.province','$.address.city') | +----------------------------------------------------------------------+ | ["tom", "湖南", "长沙"]                                              | +----------------------------------------------------------------------+ 路径表达式*的使用将会把多个路径的数据组合成数组返回12# 先插入一条用于测试的数据INSERT INTO `test_json` (`id`,`content`) VALUES(3,'{"name":"tom","address":{"name":"中央公园","city":"长沙"},"class":{"id":3,"name":"一年三班"},"friend":[{"age":20,"name":"marry"},{"age":21,"name":"Bob"}]}')12content: id=3{“name”: “tom”, “class”: {“id”: 3, “name”: “一年三班”}, “friend”: [{“age”: 20, “name”: “marry”}, {“age”: 21, “name”: “Bob”}], “address”: {“city”: “长沙”, “name”: “中央公园”}}

当开发与Linux环境下MySQL数据库交互的Java应用程序时，理解MySQL中的大小写敏感性可以避免潜在的错误和问题。本指南深入探讨了MySQL中的大小写敏感设置，比较了5.7和8.0版本，并为Java开发者提供了最佳实践。1 理解MySQL中的大小写敏感性默认情况下，MySQL在Windows上是大小写不敏感的，但在Linux上是大小写敏感的。这种差异可能导致不一致性，特别是在迁移数据库或开发跨平台应用程序时。MySQL中的大小写敏感行为由lower_case_table_names系统变量控制。lower_case_table_names = 0：表名按指定存储，比较是大小写敏感的。lower_case_table_names = 1：表名在磁盘上以小写存储，比较不是大小写敏感的。lower_case_table_names = 2：表名按指定存储，但比较不是大小写敏感的。2 MySQL 5.7大小写敏感设置在MySQL 5.7中，默认在Linux上的设置是lower_case_table_names = 0，这意味着表名是大小写敏感的。要改变这种行为，您需要明确设置lower_case_table_names变量。2.1 配置MySQL 5.7编辑MySQL配置：打开MySQL配置文件，通常位于/etc/mysql/my.cnf或/etc/my.cnf。sudo nano /etc/mysql/my.cnf添加lower_case_table_names设置：在[mysqld]部分下，添加以下行来将lower_case_table_names设置为1，以实现大小写不敏感的行为：[mysqld] lower_case_table_names=1重启MySQL服务：保存配置文件后，重启MySQL服务以应用更改：sudo systemctl restart mysql3 MySQL 8.0大小写敏感设置在MySQL 8.0中，大小写敏感行为与MySQL 5.7保持一致。然而，MySQL 8.0引入了更好的处理和更严格的检查，确保lower_case_table_names设置在服务器上保持一致。配置MySQL 8.0：编辑MySQL配置：打开MySQL配置文件，通常位于/etc/mysql/my.cnf或/etc/mysql/mysql.conf.d/mysqld.cnf。sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf添加lower_case_table_names设置：在[mysqld]部分下，添加以下行来将lower_case_table_names设置为1：[mysqld] lower_case_table_names=1重启MySQL服务：重启MySQL服务以应用更改：sudo systemctl restart mysql4 针对Java开发者的考虑在Java应用程序中使用MySQL数据库时，请考虑以下最佳实践来处理大小写敏感性：一致的命名约定：对数据库对象使用一致的命名约定。坚持使用全部小写或全部大写名称，以避免与大小写敏感性相关的问题。数据库迁移：如果从大小写不敏感的系统（如Windows）迁移数据库到大小写敏感的系统（如Linux），请确保在迁移之前适当配置lower_case_table_names设置。数据库交互：在Java中编写SQL查询时，请确保查询中使用的案例与数据库对象的案例相匹配。使用Hibernate等ORM工具可以帮助管理大小写敏感性，但正确配置它们至关重要。测试：在模拟生产设置的环境中彻底测试您的应用程序，特别是如果生产环境是大小写敏感的。文档：记录项目中使用的大小写敏感设置和命名约定。这种做法有助于保持一致性，并帮助新开发者理解项目的数据库设计。5 总结在Linux上管理MySQL的大小写敏感性对于开发健壮的Java应用程序至关重要。通过理解lower_case_table_names变量并正确配置它，确保在不同环境中的一致行为，避免与大小写敏感性相关的常见陷阱。转载自https://www.cnblogs.com/JavaEdge/p/18211836

1 mysql的安装与启动1.1 拉取mysql5.7的镜像docker pull mysql:5.71.2 运行　　docker run: 运行Docker容器的命令。　　--restart=always: 指定容器在退出时总是重新启动。这意味着，无论容器是正常退出还是异常退出，Docker将自动重新启动这个容器。　　--privileged=true: 赋予容器特权，允许它在主机上执行一些敏感操作，这通常是出于一些特殊需求的考虑，但需要注意潜在的安全风险。　　-p 13306:3306: 将主机的端口13306映射到容器的端口3306，这样外部系统可以通过主机的3306端口访问MySQL服务。　　--name mysql: 为容器指定一个名称，这里是"mysql"。　　-v /opt/mysql/mysql-master/logs:/logs: 将主机上的/opt/mysql/mysql-master/logs目录映射到容器内的/logs目录，用于存储MySQL的日志文件。　　-v /opt/mysql/mysql-master/data:/var/lib/mysql: 将主机上的/opt/mysql/mysql-master/data目录映射到容器内的/var/lib/mysql目录，用于存储MySQL的数据文件。　　-v /opt/mysql/mysql-master/conf:/etc/mysql: 将主机上的/opt/mysql/mysql-master/conf目录映射到容器内的/etc/mysql目录，用于存储MySQL的配置文件。　　-v /opt/mysql/mysql-master/my.cnf:/etc/mysql/my.cnf: 将主机上的/opt/mysql/mysql-master/my.cnf文件映射到容器内的/etc/mysql/my.cnf文件，这是MySQL的配置文件。　　-e MYSQL_ROOT_PASSWORD=mysql: 设置MySQL的root用户密码为"mysql",如果不设置，会在日志中生成一个默认root密码，通过docker logs mysql-container查看。　　-d mysql:5.7 以后台（detached）模式运行MySQL容器。 　　#如下　　docker run　 --name mysql-master　　-p 13306:3306　 -v /opt/mysql/mysql-master/logs:/logs　 -v /opt/mysql/mysql-master/data:/var/lib/mysql　 -v /opt/mysql/mysql-master/conf:/etc/mysql/conf.d　 -e MYSQL_ROOT_PASSWORD=mysql　 -d　 mysql:5.7   1.3 主机上记得把13306端口放开，或者关闭防火墙　firewall-cmd --zone=public --add-port=13306/tcp --permanent　firewall-cmd --reload 至此可以通过数据连接工具进行连接了，启动完成1.5 小记进入mysql容器，如果出现 #bash-4.2在容器中执行：cp /etc/skel/.bash* /root/2 搭建mysql集群2.1 修改主节点的onf.d文件内容　　vim /opt/mysql/mysql-slave1/my.cnf　　#加入如下配置　　server-id=1    #设置服务id，需全局唯一　　log-bin=/var/lib/mysql/mysql-bin  #加入binlog配置，供从库读取　　#binlog-do-db =test　　binlog-ignore-db=mysql　　binlog-ignore-db=sys　　binlog-ignore-db=performance_scheme　　binlog-ignore-db=information_scheme　　binlog_format=row　　#重启主节点　　docker restart mysql-master　　　2.2 master节点创建一个用户docker inspect --format='{{.NetworkSettings.IPAddress}}' mysql-master #查看主节点在容器中的ipdocker exec -it mysql-master bash #进入容器mysql -uroot -p123 #登录mysql#创建用户方式一,创号授权一步到位grant replication slave on *.* to 'slave1'@'172.17.0.2' identified by '账号密码' #创建一个名为slave1的用户并给其复制权限，可以mysql数据库中user表中查看，此为mysql5.7版本语句#创建用户方式二，创号授权分开来CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';这里的username是您要创建的用户名，hostname指定从哪些主机该用户可以连接到服务器，password是该用户的密码。您可以使用%作为通配符来允许从任何主机连接GRANT ALL PRIVILEGES ON database_name.table_name TO 'username'@'hostname';这里的database_name和table_name指定了用户被授权的数据库和表。ALL PRIVILEGES表示授予用户所有权限。您也可以根据需要授予特定权限，如SELECT, INSERT, UPDATE, DELETE等。FLUSH PRIVILEGES; 刷新权限。#查看master的position，这是个会变化的值,后面从机连接主机时会用到。show master status;2.3 docker上再创建一个mysql容器mysql-slave1　　docker run　 --name mysql-slave1　　-p 13307:3306　 -v /opt/mysql/mysql-slave1/logs:/logs　 -v /opt/mysql/mysql-slave1/data:/var/lib/mysql　 -v /opt/mysql/mysql-slave1/conf:/etc/mysql/conf.d　 -e MYSQL_ROOT_PASSWORD=mysql　 -d　 mysql:5.7　　同样要记得关闭防火墙或开放端口出去2.4 配置从库vim otp/mysql/mysql-slave1/conf/my.cnf　#加入服务id配置 [mysqld]　server-id=2 #要全局唯一docker restart mysql-slave #重启从库docker exec -it mysql-slave baash #进入从库mysql -uroot -p123 #登录mysql? change master to #此条命令可以查看配置列表模板，辅助配置#配置主库连接#将mysql设置为从库 CHANGE MASTER TO MASTER_HOST = '172.17.0.2', MASTER_USER = 'slave1', MASTER_PASSWORD = 'mysql', MASTER_PORT = 3306, MASTER_RETRY_COUNT = 0, MASTER_HEARTBEAT_PERIOD = 10000;　说明：连接master信息只配置主机名、端口、用户名、密码也足以实现连接。#开启slave同步start slave; 　　#查看是否配置成功　　show slave status\G； Slave_IO_Running、Slave_SQL_Running这两处显示两个yes，则说明主从复制建立成功,也可以在主库写入数据，在从库进行查询，如果能查到主库写入的信息，则也能说明主从关系建立成功。　至此，mysql的主从复制搭建完成，如果需要增加从节点继续创建slave即可，注意server-id不可重复。　　#若想停止从库同步　　stop slave 搭建过程中出现报错可参考：https://blog.51cto.com/u_15956038/6040698

 在MySQL中优化SQL查询是提高数据库性能的关键步骤。以下是一些常用的SQL优化方案：  1. **使用索引**：    - 为经常用于查询条件的列创建索引。    - 对于经常一起出现在WHERE子句中的列，创建复合索引。    - 避免在索引列上使用函数或表达式，因为这可能导致索引失效。  2. **优化数据类型**：    - 使用最合适的数据类型来存储数据，例如，对于电话号码使用VARCHAR而不是INT。    - 避免使用NULL，因为它会使索引、索引统计和值比较更加复杂。  3. **避免全表扫描**：    - 尽量避免在查询中使用`SELECT *`，而是明确指定需要的列。    - 使用`LIMIT`子句限制返回的行数。  4. **优化JOIN操作**：    - 确保JOIN的列都是索引的。    - 尽量减少JOIN的数量，特别是避免多个表之间的多路JOIN。  5. **优化子查询**：    - 尽量将子查询转换为JOIN，因为JOIN通常比子查询更快。    - 如果必须使用子查询，确保它返回的结果集尽可能小。  6. **使用EXPLAIN分析查询**：    - 使用`EXPLAIN`关键字可以查看MySQL如何执行查询，这有助于识别潜在的性能瓶颈。  7. **优化GROUP BY和ORDER BY**：    - 当使用`GROUP BY`或`ORDER BY`时，确保这些列是索引的。    - 尝试减少`GROUP BY`和`ORDER BY`中的唯一值数量。  8. **调整MySQL配置**：    - 根据服务器的硬件和工作负载调整MySQL的配置参数，如缓存大小、线程数等。  9. **定期维护**：    - 定期运行`OPTIMIZE TABLE`命令来重建表和索引。    - 清理不再使用的旧数据和索引。  10. **使用分区**：     - 如果表非常大，可以考虑使用分区来提高查询性能。  11. **避免使用LIKE '%...%'**：     - 这种模式的LIKE查询不能使用索引，会导致全表扫描。如果可能，尝试将其更改为LIKE '...%'。  12. **使用绑定变量**：     - 在准备语句中使用绑定变量可以提高查询性能，因为它们允许MySQL重用查询计划。  13. **避免使用临时表**：     - 某些查询可能会导致MySQL创建一个临时表，这可能会降低性能。使用`EXPLAIN`检查查询是否使用了临时表。  14. **选择合适的存储引擎**：     - 根据应用的需求选择合适的存储引擎，例如InnoDB或MyISAM。  15. **批量插入和删除**：     - 使用批量操作可以减少事务的数量，从而提高性能。  通过结合上述技术和策略，你可以显著提高MySQL的性能。但是，每个数据库和应用都是独特的，所以最好进行基准测试并根据具体情况进行调整。 

一、binlog日志介绍MySQL的二进制日志（binary log，简称binlog）是MySQL数据库中非常关键的一个组件，主要用于记录所有数据库表结构或表数据改变的操作语句（除了数据查询语句SELECT和SHOW等），并以“事件”形式存储在日志文件中。binlog是MySQL数据复制的基础，并且常常被用于数据恢复、审计等场景。1、binlog 的主要功能复制：binlog 是 MySQL 主从复制功能的基础。主服务器上的所有改变都会被记录到 binlog，然后从服务器通过读取并执行这些事件来与主服务器保持同步。数据恢复：当数据库发生错误需要恢复到某个特定点时，可以使用 binlog 来回放事件，从而达到数据恢复的目的。审计：通过分析 binlog 的内容，可以了解到数据库中发生了哪些改变，这对于数据库的审计跟踪非常有帮助。2、binlog 的工作模式binlog 有三种格式，分别是：Statement-based replication (SBR)：这种模式下，binlog 记录的是 SQL 语句的原文。它直接记录了造成数据变更的 SQL 语句，但在某些情况下可能会导致主从数据不一致（如涉及随机数或当前时间的 SQL 语句）。Row-based replication (RBR)：在这种模式下，binlog 不记录 SQL 语句的内容，而是记录行的更改。即每一行数据改变前后的具体值都会记录下来。这种模式数据量可能会比较大，但可以避免 SBR 模式下的数据一致性问题。Mixed-based replication (MBR)：这是一种混合模式，MySQL 会根据具体的操作自动选择使用 SBR 或 RBR。默认情况下，MySQL 会尽可能使用 SBR，因为它更为高效。但在可能导致数据不一致的情况下，会自动切换到 RBR。3、binlog 的配置与管理在 MySQL 配置文件（通常是 my.cnf 或 my.ini）中可以设置 binlog 相关的参数。启用 binlog：12[mysqld]log_bin = /var/log/mysql/mysql-bin.log设置 binlog 格式：12[mysqld]expire_logs_days = 7查看 binlog 文件：1SHOW BINARY LOGS;查看 binlog 的内容：1mysqlbinlog /path/to/bin-log-file4、注意事项使用 binlog 需要考虑磁盘空间，因为所有的数据变更都会被记录，可能会占用大量磁盘空间。在使用 binlog 进行数据恢复或复制时，需要确保所有相关的 binlog 文件都是完整且连续的，从而避免数据丢失。binlog 是 MySQL 中一个非常强大的功能，可以用来实现数据的复制、恢复和审计。正确配置并有效管理 binlog 对维护数据库的稳定性和数据的完整性至关重要。二、Binlog与数据库迁移我有一个Mysql数据库，需要从A服务器迁移到B服务器，使用binlog怎么迁移？使用 MySQL 的 binlog 迁移数据通常涉及到数据的实时复制，适用于主从复制的设置，而不是一次性的全量数据迁移。如果你的目标是将一个数据库从服务器 A 迁移到服务器 B，并且希望在迁移过程中数据库保持服务，可以设置 A 为主服务器，B 为从服务器，通过主从复制来同步数据。一旦数据同步完成，可以通过切换使 B 服务器成为新的主服务器。下面是使用 binlog 进行数据库迁移的基本步骤：1. 准备服务器 B确保服务器 B 已经安装了 MySQL 并且版本与服务器 A 相兼容。配置好 MySQL 的网络访问权限，确保服务器 A 可以连接到服务器 B。2. 配置服务器 A在服务器 A 上，你需要确保 binlog 已经启用，并且记录了所有必要的操作。可以通过查看配置文件（通常是 my.cnf 或 my.ini）确认这一点。1234[mysqld]log_bin = /var/log/mysql/mysql-bin.logserver_id = 1binlog_format = MIXED  # 或 ROW，视具体情况选择3. 创建数据快照在服务器 A 上锁定数据库以创建一个一致的数据快照。这通常涉及到锁定所有数据库表，确保在数据传输过程中没有更改。12FLUSH TABLES WITH READ LOCK;SHOW MASTER STATUS;记录 SHOW MASTER STATUS 输出的结果，特别是 File 和 Position，这将是复制的起点。4. 导出数据还在持有锁的情况下，使用 mysqldump 或其他数据库导出工具导出数据库：1mysqldump -u root -p --all-databases --master-data > dbdump.sql5. 解锁表一旦数据导出完成，可以解锁表：1UNLOCK TABLES;6. 导入数据到服务器 B将导出的数据文件 dbdump.sql 移动到服务器 B，并导入数据：1mysql -u root -p < dbdump.sql7. 配置服务器 B 作为从服务器在服务器 B 的配置文件中设置以下参数：12345[mysqld]server_id = 2relay_log = /var/log/mysql/mysql-relay-bin.loglog_bin = /var/log/mysql/mysql-bin.logread_only = 1然后设置服务器 B 以服务器 A 为主服务器：123456CHANGE MASTER TOMASTER_HOST='server_A_IP',MASTER_USER='replication_user',MASTER_PASSWORD='password',MASTER_LOG_FILE='recorded_log_file_name',MASTER_LOG_POS=recorded_log_position;使用 SHOW MASTER STATUS; 在服务器 A 上获取的 recorded_log_file_name 和 recorded_log_position。8. 启动复制在服务器 B 上启动复制进程：1START SLAVE;9. 监控复制状态检查复制状态，确保没有错误：1SHOW SLAVE STATUS\G10. 完成迁移一旦服务器 B 的状态显示复制已经追上服务器 A，且 Seconds_Behind_Master 为 0，你可以考虑切换应用程序到新的服务器 B 或者提升服务器 B 为新的主服务器。这些步骤提供了使用 binlog 和主从复制进行数据库迁移的概览。实际操作中可能需要根据具体情况调整步骤和参数。三、binlog日志与数据恢复使用 MySQL 的 binlog 日志进行数据恢复是一种有效的方法，尤其适用于从意外数据丢失、错误操作或数据库损坏中恢复数据。binlog 包含了所有修改数据库数据的 SQL 语句（除数据查询语句以外），因此可以用来复现数据库状态。以下是使用 binlog 进行数据恢复的基本步骤：1. 确保你有完整的 binlog 文件首先，确保你有从数据丢失前到恢复点之间的所有 binlog 文件。你也需要有最近的完整数据库备份。binlog 用于从这个备份点向前“回放”操作。2. 准备恢复环境为了安全起见，建议在一个单独的恢复环境（如新的服务器或本地机器）上进行数据恢复操作，而不是直接在生产数据库上操作。这可以避免对现有数据造成进一步的损害。3. 将数据库恢复到最近的全备份在恢复环境上，首先将数据库恢复到最近的全备份状态。这通常使用 mysql 命令加载一个 SQL 备份文件，例如：1mysql -u username -p database_name < full_backup.sql4. 使用 mysqlbinlog 工具处理 binlog使用 mysqlbinlog 工具来处理并应用 binlog 文件。如果你知道具体的事件位置或时间，你可以使用 --start-position 或 --stop-position（根据日志文件中的位置），或者 --start-datetime 和 --stop-datetime（根据时间）来限制处理的日志范围。例如，如果你想从某个时间点开始应用 binlog：1mysqlbinlog --start-datetime="2024-01-01 00:00:00" /path/to/bin-log-file | mysql -u username -p database_name如果你有多个 binlog 文件，则需要按顺序处理它们，确保按照生成的时间顺序来应用。5. 检查和验证数据在应用了 binlog 文件后，仔细检查和验证恢复的数据是否正确。可能需要进行一些手动调整以确保数据的完整性和一致性。6. 反思和改进备份策略在数据恢复过程结束后，评估当前的备份和恢复策略的有效性。考虑是否需要更频繁的备份，或者引入其他类型的备份（如增量备份）来改进数据安全性。注意事项安全性：处理 binlog 时，考虑到里面可能包含敏感信息。确保在安全的环境下操作，避免数据泄露。数据一致性：在使用 binlog 恢复数据时，确保应用的 binlog 与备份数据的时间线相匹配，防止数据不一致。错误操作：在恢复前应确认 binlog 中的操作是否为误操作，避免重复应用可能导致的错误。通过上述步骤，你可以有效地利用 MySQL 的 binlog 日志来恢复数据。适当地管理和测试恢复流程是确保在实际发生灾难时能快速有效恢复数据的关键。

截取函数substr()方法以及参数详解1、substr(str, position)   从position截取到字符串末尾str可以是字符串、函数、SQL查询语句position代表起始位置，索引位置从1开始123select substr(now(), 6);select substr('2023-10-25', 6);select substr((select fieldName from tableName where condition), 1);2、substr(str from position)  从position截取到字符串末尾和1的操作类似，和上面1的操作对比可以发现只是把括号中的逗号换成from关键字123select substr(now() from 6);select substr('2023-10-25' from 6);select substr((select fieldName from tableName where condition) from 1);3、substr(str, position, length)  从position截取长度为length的字符串str可以是字符串、函数、SQL查询语句position代表起始位置length代表截取的字符串长度123select substr(now(), 1, 4);select substr('2024-01-01', 1, 4);select substr((select fieldName from tableName where condition), 1, 4);4、substr(str from position for length)  从position截取长度为length的字符串和3的操作类似，和上面3的操作对比可以发现只是把括号中的第一个逗号换成from关键字，第二个逗号换成了for关键字123select substr(now() from 6 for 5);select substr('2023-10-01' from 6 for 5);select substr((select fieldName from tableName where condition) from 6 for 5);

在SQL中，COALESCE函数是一个非常有用的函数，用于从其参数列表中返回第一个非NULL值。如果所有给定的参数都是NULL，那么COALESCE函数将返回NULL。这个函数可以接受多个参数，使其在处理可能出现的NULL值时非常灵活和强大。语法1COALESCE(expression1, expression2, ..., expressionN)expression1, expression2, ..., expressionN：是COALESCE函数要检查的表达式列表。函数会从左到右评估这些表达式，返回第一个非NULL的表达式值。使用场景默认值设置：当你希望某个列或表达式返回一个默认值（而不是NULL）时，COALESCE可以提供这个默认值。这对于数据报告和用户界面显示特别有用，因为你可以避免显示NULL值，而是显示一个更有意义的默认值。数据清洗：在处理含有NULL值的数据时，COALESCE可以帮助你将这些NULL值转换为实际的数值或文本，便于分析和计算。条件选择：COALESCE可以用于基于数据存在性（是否为NULL）条件性地选择值。示例假设你有一个Employees表，其中包含员工的salary列，你想要选择一个列，显示员工的薪水，如果薪水是NULL，则显示0。1SELECT COALESCE(salary, 0) AS effective_salary FROM Employees;这个查询通过COALESCE函数确保了effective_salary列不会包含NULL值；如果salary是NULL，则effective_salary会显示为0。小结COALESCE函数提供了一种简单有效的方式来处理SQL查询中的NULL值，使得数据分析和展示更加灵活和清晰。它是处理NULL值时应该考虑的首选函数之一，特别是当你需要从一组可能的NULL值中选择第一个实际存在的值时。leetcode例题：1378. 使用唯一标识码替换员工ID题目描述Employees 表：12345678+---------------+---------+| Column Name   | Type    |+---------------+---------+| id            | int     || name          | varchar |+---------------+---------+在 SQL 中，id 是这张表的主键。这张表的每一行分别代表了某公司其中一位员工的名字和 ID 。EmployeeUNI 表：12345678+---------------+---------+| Column Name   | Type    |+---------------+---------+| id            | int     || unique_id     | int     |+---------------+---------+在 SQL 中，(id, unique_id) 是这张表的主键。这张表的每一行包含了该公司某位员工的 ID 和他的唯一标识码（unique ID）。展示每位用户的 唯一标识码（unique ID ）；如果某位员工没有唯一标识码，使用 null 填充即可。你可以以 任意 顺序返回结果表。返回结果的格式如下例所示。示例 1：12345678910111213141516171819202122232425262728293031323334输入：Employees 表:+----+----------+| id | name     |+----+----------+| 1  | Alice    || 7  | Bob      || 11 | Meir     || 90 | Winston  || 3  | Jonathan |+----+----------+EmployeeUNI 表:+----+-----------+| id | unique_id |+----+-----------+| 3  | 1         || 11 | 2         || 90 | 3         |+----+-----------+输出：+-----------+----------+| unique_id | name     |+-----------+----------+| null      | Alice    || null      | Bob      || 2         | Meir     || 3         | Winston  || 1         | Jonathan |+-----------+----------+解释：Alice and Bob 没有唯一标识码, 因此我们使用 null 替代。Meir 的唯一标识码是 2 。Winston 的唯一标识码是 3 。Jonathan 唯一标识码是 1 。解答要解决这个问题，你可以使用 SQL 的 LEFT JOIN 语句来连接 Employees 表和 EmployeeUNI 表，并且使用 COALESCE 函数来处理那些没有匹配 unique_id 的情况，将它们填充为 NULL。LEFT JOIN 会返回左表 (Employees) 的所有行，如果左表的行在右表 (EmployeeUNI) 中没有匹配行，则结果中对应行的 EmployeeUNI 表列会包含 NULL 值。以下是实现该逻辑的 SQL 查询：123456789SELECT    COALESCE(EU.unique_id, NULL) AS unique_id,     E.nameFROM    Employees ELEFT JOIN    EmployeeUNI EU ON E.id = EU.idORDER BY    E.id; -- 或者根据需要排序，比如按照 name 或 unique_id这个查询做了以下事情：FROM Employees E - 从 Employees 表开始，为表设置了一个别名 E 以简化后续引用。LEFT JOIN EmployeeUNI EU ON E.id = EU.id - 通过 LEFT JOIN 将 Employees 表和 EmployeeUNI 表连接起来，基于两表的 id 字段。EmployeeUNI 表也被赋予了别名 EU。COALESCE(EU.unique_id, NULL) AS unique_id - COALESCE 函数返回其参数列表中的第一个非 NULL 值。在这里，如果 EU.unique_id 是 NULL（意味着 LEFT JOIN 没有找到匹配的行），则结果仍然是 NULL。虽然在这种情况下使用 COALESCE 函数可能看起来多余（因为 EU.unique_id 本身在没有匹配的情况下就是 NULL），但它在这里说明了如何处理可能的 NULL 值。实际上，你可以直接选择 EU.unique_id。ORDER BY E.id - 结果按照员工的 id 排序。这一步是可选的，取决于你想如何展示结果。注意，这个查询确保了即使某些员工没有对应的 unique_id，他们的名字仍然会出现在查询结果中，unique_id 列用 NULL 表示他们缺少唯一标识码。

从宏观角度来看，MySQL 可以分为两部分：Server 层：是客户端与存储引擎的中间层，提供了 MySQL 对外暴露的所有功能存储引擎层：负责数据的实际存储和读取Server 层功能模块MySQL 的 Server 层提供了对外暴露的所有功能，例如请求连接、认证、语法/词法分析、执行语句优化、查询缓存、内置函数、语句执行等功能。这些功能分别由不同的功能模块提供，模块与模块之间的分工非常明确；同时，也是由这些模块的相互协作，最终给我们提供了可用的 MySQL 服务。连接器连接器是负责与客户端建立连接，权限管理，以及管理连接的功能模块。连接器的功能职责比较清晰，但也有些细节需要关注：建立连接时，如果认证成功，连接器会把当前时刻的用户权限快照作为这个连接后续的权限判断逻辑的依据，直到连接断开这也意味着，当一个用户成功建立了连接后，即使对这个用户的权限进行了修改，也不会影响已经存在的连接权限判断连接建立成功后，连接的最大空闲时间（即什么操作都不执行的时间）由 wait_timeout 参数控制，默认为 8 小时如果在空闲时间超时后，再发送操作请求，那么将会收到 MySQL 返回的错误消息：Lost connection to MySQL server during query如果在空闲时间超时后，想要再次正常地发送请求，那么需要重新建立连接查询缓存查询缓存，主要用于将相同的查询语句的结果给缓存起来。查询缓存的工作原理如下所示：执行查询语句之前，MySQL 会在内存中查看之前是否执行过相同的（一模一样的）语句如果有，那么直接将缓存中的结果集返回，并结束本次的查询语句执行流程如果没有，那么将正常走后面的逻辑，直到拿到结果集查询缓存拿到结果集后，将本条查询语句与查询得到的结果集以 K-V 的形式缓存到内存中将结果集返回，本次查询语句执行流程结束既然有缓存，那么就需要考虑数据一致性的问题。MySQL 给出的解决方案就是当一个表进行了更新/新增操作后，这个表上的所有查询缓存都会失效。这样一来，查询缓存的功能就变得非常鸡肋了。具体的原因有：查询语句完全相同的概率可能并不高执行缓存操作本身也是需要耗费时间和空间的最主要的原因是清空缓存的触发条件过于简单，但是影响却十分巨大（只有表上有一个更新/新增操作，那么这个表上的所有查询缓存都会被清空）综合来说，即在大多数场景下，查询缓存带来的性能提升效果可能比不上执行缓存操作本身带来的性能消耗，即查询缓存不值得使用。当然，在一些几乎完全不会发生更新/新增操作的表上，这个查询缓存还是可能会起到提升性能的作用的。MySQL 8.0 之前可以通过将 query_cache_type 改为 DEMAND，并在查询语句的查询返回字段前增加 SQL_CACHE 关键字来显示指定使用查询缓存，例如：1select SQL_CACHE * from user where id = 1;需要注意的是，MySQL 在 8.0 及之后的版本将查询缓存功能彻底移除了。分析器在客户端向服务端发送了一条 SQL 语句之后，MySQL 需要分析这条 SQL 语句是否合法；如果合法，那么这条 SQL 语句究竟是想要执行什么操作。这就是分析器的职责。分析的过程，主要分为词法分析和语法分析词法分析：将输入的 SQL 语句中的所有单词（由空格隔开的字符串）识别为不同的含义例如，把 select、update 给识别出来这是一个操作关键字，把输入的 distinct 识别为一个去重的关键字语法分析：根据词法分析的结果以及当前配置的 SQL 执行模式（sql_mode 参数），判断这条 SQL 语句是否合法如果不合法，那么将会直接返回一个语法错误如果合法，那么 MySQL 就会将 SQL 语句的执行意图给解析出来优化器在经过分析器的词法分析和语法分析后，SQL 语句的执行流程就来到了优化器。由上面的逻辑我们可以知道，到达优化器的语句必定是一个合法的，且执行意图已知的 SQL 语句。优化器的作用，就是尝试为 SQL 语句的执行意图，挑选出一种效率最高的执行方案。例如，在一个要执行查询语句的目标数据表中，可能存在多个索引，优化器将会根据这些索引的类型以及字段组合，结合查询语句本身的条件，为其挑选一个最优的索引，以便用于后续真正的数据查询。又或者，在一个有多表关联的查询语句中，根据表连接的字段以及各表的数据量，决定表与表之间的连接顺序以及使用的算法。优化器的工作结束后，这条语句的执行方案就确定下来了。值得一提的是，我们使用 explain 关键字用于分析一条 SQL 语句的执行计划时，返回的正是优化器的一部分分析结果。执行器MySQL 通过分析器已经知道了 SQL 语句的执行意图，并且通过优化器已经为这条 SQL 语句挑选除了一种效率最高的执行方案，那么 SQL 语句的执行流程将会来到执行器。执行器的主要工作为：首先查看当前用户是否具有 SQL 语句中的目标表的对应操作权限如果没有，例如当前用户没有对于目标表的查询权限，那么将会直接返回权限错误如果有权限，那么将会调用当前使用的存储引擎的对应操作接口，执行这条 SQL 语句真正的执行意图例如，当前使用的存储引擎是 InnoDB，当前执行的 SQL 语句是 select * from user where id=1，那么执行器将会调用存储引擎层的查询接口执行对于 user 表的具体查询操作存储引擎层存储引擎层负责真正的数据存储和提取，其结构（对于 Server 层来说）是插件式的，封装了具体的存储引擎的操作逻辑。存储引擎的服务对象是表。意思就是说，不同的表可以使用不同的存储引擎；同一个数据库中的不同表也可能使用不同的存储引擎。下面将介绍常用的两个存储引擎：InnoDB 和 MyISAM。InnoDBInnoDB 是 MySQL 5.5 版本后的默认存储引擎，也是日常开发过程中使用的最多的存储引擎。使用 InnoDB 作为引擎来存储的表， 会对应磁盘上的两个文件：*.ibd（索引及数据文件），存储的是聚集索引（索引与数据在同一棵 B+ 树中），以及非聚集索引*.frm（表结构文件）存储的是表结构InnoDB 主要特性InnoDB 的主要特性如下所示：支持事务支持更细粒度的锁（行锁）拥有崩溃后安全恢复（crash safe）能力支持外键InnoDB 引擎下的查询过程在使用了 InnoDB 引擎的表的单表查询语句的执行过程将会是这样：首先看查询条件中是否使用了聚集索引如果有，直接在聚集索引中进行 B+Tree 查找直到找到数据，并将数据返回，时间复杂度为 O（logN）如果不是聚集索引，则看查询条件中是否命中的二级索引（非聚集索引）如果可以命中二级索引，则首先在对应的二级索引树中查找，如果找到了，则取叶子节点上的聚集索引值，再回到聚集索引中（使用刚刚查找到的聚集索引值）进行查询（回表操作），并将数据返回，时间复杂度为 O（logN）如果二级索引也不能命中，则直接在聚集索引树中遍历所有叶子节点，待全表扫描完后，再将中途查找到的符合条件的所有数据返回，时间复杂度为 O（N）MyISAMMyISAM 是 MySQL 最早出现的一批存储引擎之一，但是现在在日常开发过程中已经比较少用。 MyISAM 也有很多优点，但是有一个致命的缺点：不支持事务，没有 crash safe 能力。使用 MyISAM 作为引擎来存储的表， 会对应磁盘上的三个文件：*.myi（索引文件）存储的是非聚集索引，叶子节点上存储的是数据对应的地址（.myd 文件中的位置）*.myd（数据文件），存储的是实际的数据*.frm（表结构文件），存储的是表结构MyISAM 的主要特性MyISAM 的主要特性如下所示：只支持表锁内置了一个计数器来存储表的行数延迟更新索引键：如果在创建表时指定了 DELAY_KEY_WRITE 参数，那么每次更新了（索引相关的）数据后，并不会立刻将修改的索引数据写入磁盘中，而是采用了缓冲区+延时批量写入的设计来延后地、批量地写入更新的索引数据。这样可以极大地提升写入的性能设计简单，数据以紧密的格式存储：在更新较少的场景下性能表现很好MyISAM 引擎下的查询过程在使用了 MyISAM 引擎的表的单表查询语句的执行过程将会是这样：首先看查询条件中，是否有可以命中的索引如果有，则在索引文件中进行 B+Tree 查找直到找到数据的地址，然后再通过数据地址在数据文件中找到对应的数据，时间复杂度为 O（logN）如果没有，则在数据文件中遍历所有数据行，待全表扫描完成后，再将中途查找到的符合条件的所有数据返回，时间复杂度为 O（N）InnoDB 和 MyISAM 的对比InnoDB 和 MyISAM 的主要区别有：MyISAM 不支持事务，InnoDB 支持事务：两个存储引擎最大的两个区别之一，MyISAM 不支持事务的特性导致了它在注重数据一致性的场景下无法使用MyISAM 不支持崩溃后的安全恢复（crash safe），而 InnoDB 则支持：也是两个存储引擎最大的两个区别之一，MyISAM 不支持 crash safe 导致了它在注重数据安全的场景下无法使用MyISAM 只支持表锁，而InnoDB 既支持表锁也支持行级锁：MyISAM 只支持表锁的特性，在更新操作稍多的场景下，读写性能会大幅下降，这也导致了在这种场景下 MyISAM 的使用率将会比较低对表的行数查询的支持不同：MyISAM 内置了一个计数器来存储表的行数，在需要查询表的行数时直接从计数器中拿出即可InnoDB 需要去统计所有的行数，在高版本的 MySQL 中，InnoDB 也会有一个存了行数的变量，但这只是个估计值，需要准确的值时仍需要去实时统计MyISAM 不支持外键，InnoDB 支持外键delete from table 的处理方式不一样：MyISAM直接重新建表InnoDB 会一行一行的删除文件存储方式不同：MyISAM ：一个表在磁盘上对应三个文件：*.myi（索引文件）、*.myd（数据文件）、 *.frm（表结构文件）Innodb：一个表在磁盘上对应两个文件：*.ibd（数据及索引文件）、 *.frm（表结构文件）总的来说，在不考虑数据一致性以及数据安全性，且查询操作远多于更新操作的场景下，可以考虑选择 MyISAM 作为存储引擎；否则都应该选择 Innodb 引擎

过滤和排序查询结果在数据库中是非常常见和重要的操作。通过过滤和排序，可以精确地从数据库中检索出所需的数据，并且以特定的顺序呈现。下面将详细介绍如何使用SQL语句来进行过滤和排序查询结果，帮助你更好地理解和应用数据库查询操作。过滤查询结果使用WHERE子句进行条件筛选 在SQL中，使用WHERE子句可以根据指定的条件筛选出符合条件的数据。条件可以使用比较运算符（=、<、>等）、逻辑运算符（AND、OR）和通配符（LIKE）进行组合。以下是一个简单的例子：1SELECT * FROM table_name WHERE column_name = value;上述语句将从指定表中检索出满足条件的行，其中column_name的值等于指定的value。排序查询结果使用ORDER BY子句进行结果排序 通过使用ORDER BY子句，可以对检索结果进行排序，可以按照一个或多个列的升序或降序进行排序。以下是一个示例：1SELECT * FROM table_name ORDER BY column_name;上述语句将按照指定的column_name对检索结果进行升序排序。指定多个排序条件 除了单个列的排序之外，还可以指定多个列，并使用逗号进行分隔，以实现多条件排序：1SELECT * FROM table_name ORDER BY column1, column2 DESC;上述语句将按照column1的升序排序，对于相同的column1值，再按照column2的降序排序。示例让我们通过一个简单的示例来演示过滤和排序查询结果的操作。假设我们有一个名为"products"的表，包含产品的名称和价格信息。我们想要检索出价格大于100的产品，并且按照价格进行降序排序：1SELECT * FROM products WHERE price > 100 ORDER BY price DESC;复制这条SELECT语句将返回价格大于100的产品，并按照价格的降序进行排序。