MySQL

MySQL 分页 一般MySQL最基本的分页方式: select * from content order by id desc limit 0, 10 在中小数据量的情况下，这样的SQL足够用了，唯一需要注意的问题就是确保使用了索引。随着数据量的增加，页数会越来越多，查看后几页的SQL就可能类似: select * from content order by id desc limit 10000, 10 一言以蔽之，就是越往后分页，LIMIT语句的偏移量就会越大，速度也会明显变慢。 此时，我们可以通过2种方式: 一，子查询的分页方式来提高分页效率，SQL语句如下: SELECT * FROM content WHERE id <= (SELECT id FROM content ORDER BY id desc LIMIT “.($page-1)*$pagesize.”, 1) ORDER BY id desc LIMIT $pagesize 为什么会这样呢？因为子查询是在索引上完成的，而普通的查询时在数据文件上完成的，通常来说，索引文件要比数据文件小得多，所以操作起来也会更有效率。 (via) 通过explain SQL语句发现: 子查询使用了索引！ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY content range PRIMARY PRIMARY 4 NULL 6264 Using where ...

JDBC URL disable tls JDK8版本过高引起MySQL连接失败：javax.net.ssl.SSLHandshakeException: No appropriate protocol >[https://juejin.cn/post/6969142310718144520](https://juejin.cn/post/6969142310718144520) jdbc:mysql://127.0.0.1/database0?useunicode=true&characterencoding=utf8&tinyInt1isBit=false&useSSL=false MySQL MySQL Connector/J Driver 驱动程序包: http://mvnrepository.com/artifact/MySQL/MySQL-connector-java 驱动程序类名: com.MySQL.jdbc.Driver JDBC URL Format The general format for a JDBC URL for connecting to a MySQL server is as follows, with items in square brackets ([ ]) being optional: jdbc:MySQL://[host1][:port1][,[host2][:port2]]…[/[database]] [?propertyName1=propertyValue1[&propertyName2=propertyValue2]...] Here is a simple example for a connection URL: jdbc:MySQL://localhost:3306/sakila?profileSQL=true JDBC URL: jdbc:MySQL://<host>:<port>/<database_name> 默认端口3306，如果服务器使用默认端口则port可以省略 MySQL Connector/J Driver 允许在URL中添加额外的连接属性jdbc:MySQL://<host>:<port>/<database_name>?property1=value1&property2=value2 常用的有两个，一个是gjt (Giant Java Tree) 组织提供的MySQL驱动，其JDBC Driver名称 (JAVA类名) 为: org.gjt.mm.MySQL.Driver ...

背景 2017 年前后，我在大连 R 公司负责 R 平台协议解析层的开发和维护。R 平台是一套车联网平台，部署在 H 云上，用来接收和处理车载设备实时上报的数据。 协议解析层有十五个以上的服务，每个服务至少两个节点，负责解析车载设备上报的自定义 TCP 协议，网络通信与连接处理底层由 Netty 实现。平台共有 300 多台虚拟机，单个服务的规格通常不会拉很高，G4 所在的虚拟机配置是 2 CPU、4 GB 内存。 其中 G4 是协议解析层数据量最大的服务，负责解析行车数据，单条消息包含约两百个字段，涵盖车速、转速、门窗锁状态、发动机参数、各类液体温度、油量等实时车况信息。正常 QPS 约 700 条/秒，峰值超过 1000 条/秒，四个节点加起来总 QPS 在 5000 左右。 解析完成后，G4 把数据写入 Redis（实时数据）和 MySQL（行车历史数据，水平分片成 1024 个库），另外还有一些平台内部的 API 调用。MySQL 访问层是在 JDBI 基础上封装了一层。 协议解析层的数据来自上游接入层，接入层向协议解析层推送数据，同时用 RabbitMQ 做消息队列缓冲。 装车增长与早期告警 R 平台接入的设备数量持续增长，每个月有几千台新设备安装到实车上。随着在线行驶车辆增多，G4 的数据量也跟着涨。 大规模故障之前，G4 就开始在早高峰和晚高峰偶发内存告警，高峰过去后十几分钟会自行恢复。当时不清楚具体原因，尝试过把业务线程数从默认值增加到 16，改完之后等服务低峰期重启，观察下一次高峰是否有改善。效果不明确，可能有一定缓解，也可能影响有限，之后就转向其他方向排查，线程数就维持在 16 没有再改。 大规模 OOM 装车量继续增加，某天 G4 出现了大规模 OOM。现象是四个节点滚动离线：一个节点 OOM 重启，剩余节点压力上涨，接着下一个离线，重启后很快再 OOM，如此循环。 节点重启之后撑不了太久，因为上游 RabbitMQ 里已经积压了大量数据，积压时间估计有一两个小时。一个节点恢复，积压数据涌入，Netty 堆外内存里挤满了等待处理的业务数据，内存很快再次撑满。一个节点离线，剩余三个节点分摊的流量更大，内存涨得更快，形成连锁反应。节点之间的 OOM 间隔只有几分钟。 ...

背景 在业务增长阶段，很多系统都会遇到类似问题：写入路径以 insert 为主，单条记录字段很多，写入频率持续升高，最终出现写入延迟抬升、队列积压、应用侧内存压力变大。 这类问题通常被称为“宽表高频写入瓶颈”。这里把它拆开讲清楚：为什么会慢、先做什么优化、以及什么时候该考虑从关系型数据库迁移到时序数据库。 一个真实案例可参考：物联网平台协议解析服务生产环境 OOM：MySQL 迁移 InfluxDB。 为什么宽表高频写入容易慢 宽表写入慢，通常不是单点问题，而是多个开销叠加： 单行体积大：每次写入涉及更多页修改和日志写入。 索引维护成本高：索引越多，单次 insert 需要更新的 B+Tree 越多。 随机写放大：页分裂、二级索引回表结构维护，会放大 I/O。 事务与刷盘约束：redo/binlog/fsync 的组合决定了吞吐上限。 后台线程竞争：flush、checkpoint、purge 跟前台写入争抢资源。 这里的“更多页”并不是指“每一行一定跨很多页”，而是指一次写入会牵动更多数据页：InnoDB 以页（默认常见为 16 KB）为最小读写单位。行越宽，单页可容纳的记录数越少，同样数据量需要更多页；插入时更容易触发页满和页分裂。同时，写入不只改数据页，还会改聚簇索引页、二级索引页以及对应日志；如果包含较大 TEXT/BLOB 字段，还可能涉及溢出页。结果是单次写入的页修改与日志写入成本一起上升。 在高并发下，这些开销叠加后，常见表现是：写入 RT 上升，连接占用时间变长，线程池阻塞，进而把上游消费链路拖慢。 先做 MySQL 侧排查和优化 在决定换库前，建议先把 MySQL 侧可优化项跑一遍： 表结构：拆分冷热字段，把大字段从核心写入表拆出去。 索引策略：只保留必要索引，避免“为查而建”过多索引拖慢写入。 主键设计：优先使用短且有序的主键，降低页分裂概率。 批量写入：小批次 batch insert，减少事务提交次数。 连接池与并发：避免连接池过大导致数据库抖动，结合压测设上限。 存储参数：检查 innodb_buffer_pool_size、innodb_flush_log_at_trx_commit、sync_binlog 等配置是否与场景匹配。 观测指标：重点盯写入 RT、TPS、磁盘写 IOPS、redo 生成速率、checkpoint age、锁等待。 如果这些动作做完后仍然无法满足目标吞吐，再考虑架构级改造更稳妥。 什么时候考虑时序数据库 当业务数据明显符合以下特征时，时序数据库通常更有优势： 写多读少，且主要是追加写。 读请求以时间范围查询为主。 业务天然带时间戳主维度。 接受按保留策略做冷热分层与过期清理。 这类场景下，时序引擎在写路径和压缩存储上的结构性优势，往往比继续堆关系库参数更有效。 迁移建议 从 MySQL 到时序库，建议用“双写 + 渐进切读”： 先双写，确保新旧链路数据可对账。 下游按服务分批切读，避免一次性切换。 保留回退开关，出现异常可快速切回旧链路。 观察一段稳定窗口后，再下线旧写入逻辑。 InfluxDB 相关基础可参考：InfluxDB。 ...

MySQL 大表优化 这篇是我基于一篇早期讨论内容整理的实践笔记，重点放在“遇到大表性能问题时，按什么顺序处理更稳妥”。 原讨论链接： https://www.zhihu.com/question/19719997/answer/81930332 处理顺序（先低成本，再高成本） 大多数系统都适合按以下顺序推进，避免一上来就分库分表： 先优化 SQL 和索引。 再加缓存（如 Redis），减少数据库读压力。 再做读写分离（主从）或副本扩展。 再评估分区表（前提是查询条件能命中分区键）。 再做垂直拆分（按业务域拆库）。 最后才是水平拆分（分库分表 + 路由）。 核心原因是：越往后改造成本越高，业务侵入越强，回退也越困难。 SQL 与索引是第一优先级 很多慢库问题并不是“数据库不行”，而是这三件事没有做透： SQL 写法没有针对索引设计。 索引数量过多或选择不对，拖慢写入。 优化单条 SQL 时忽略了全局负载，出现“局部变快、整体变慢”。 建议先做这套最小闭环： 用慢查询日志找 Top N SQL。 用 EXPLAIN 验证执行计划。 在压测环境对比“改前/改后”整体 RT、QPS、CPU、IO。 分区、垂直拆分、水平拆分怎么选 分区表 适合“时间范围明显、查询常带分区键”的表。优点是对业务代码相对透明；缺点是分区命中差时容易扫全分区。 垂直拆分 适合按业务域解耦，把不同模块压力拆到不同库。它能降低单实例总压力，但不能解决单表本身太大的问题。 水平拆分 适合单表已达到容量或吞吐瓶颈。收益大，但代价也最大： 需要稳定的分片键设计。 SQL 路由、聚合查询、跨分片事务复杂度明显上升。 常常需要改表结构、做冗余字段、改应用代码。 关于 InnoDB 与 MyISAM 的取舍 在当前线上系统里，绝大多数场景优先 InnoDB： 支持事务与行级锁。 并发写能力更稳。 故障恢复能力更好。 MyISAM 在某些纯读场景可能有优势，但在写多或读写混合场景里，通常不是优先选项。 实战建议 如果你正在处理“大表 + 写入变慢”，建议按下面清单落地： 先确认瓶颈在 SQL、锁、IO、还是连接池。 明确读写模型（读多写少、写多读少、时间范围查询比例）。 先完成 SQL/索引/缓存优化，再决定是否拆分。 如果进入拆分阶段，先做垂直拆分，再做水平拆分。 延伸阅读 MySQL 宽表写入性能问题：为什么会慢，何时该考虑时序库 数据库水平拆分垂直拆分 MySQL INNODB

MySQL分区表, partition 没有分区的表创建分区 partition management on a not partitioned table is not possible ALTER TABLE table0 PARTITION BY RANGE (to_days(field0)) ( PARTITION p201908 VALUES less THAN (737668), PARTITION p201909 VALUES LESS THAN (737698), PARTITION p201910 VALUES LESS THAN (737729), PARTITION p201911 VALUES LESS THAN (737759), PARTITION p201912 VALUES LESS THAN (737790) ) ALTER TABLE table0 PARTITION BY RANGE (to_days(field0)) ( PARTITION p202007 VALUES less THAN (to_days('2020-08-01')), PARTITION p202008 VALUES less THAN (to_days('2020-09-01')), PARTITION p202009 VALUES less THAN (to_days('2020-10-01')), PARTITION p202010 VALUES less THAN (to_days('2020-11-01')), PARTITION p202011 VALUES less THAN (to_days('2020-12-01')), PARTITION p202012 VALUES less THAN (to_days('2021-01-01')), ) 添加分区 alter TABLE table0 add PARTITION ( PARTITION p201908 VALUES less THAN (737668)); alter table table0 add PARTITION ( PARTITION pmax VALUES less than MAXVALUE); -- check partition size USE information_schema; SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 't0' and PARTITION_NAME='p0'; -- drop partition ALTER TABLE `db0`.t0 DROP PARTITION p0; 查看分区的存储情况 SELECT partition_name, PARTITION_DESCRIPTION, PARTITION_EXPRESSION, table_rows FROM information_schema.partitions WHERE table_name = 'table0' 查询 查询的where条件要写完整的时间范围, 只写> 或者只写<, 会导致查询所有的分区. ...

‘MySQL btree索引 hash索引’ MySQL, b tree索引 hash索引, B树 http://www.cnblogs.com/vicenteforever/articles/1789613.html Hash 索引结构的特殊性,其检索效率非常高,索引的检索可以一次定位,不像B-Tree 索引需要从根节点到枝节点,最后才能访问到页节点这样多次的IO访问,所以 Hash 索引的查询效率要远高于 B-Tree 索引。可 能很多人又有疑问了,既然 Hash 索引的效率要比 B-Tree 高很多,为什么大家不都用 Hash 索引而还要使用 B-Tree 索引呢？任何事物都是有两面性的,Hash 索引也一样,虽然 Hash 索引效率高,但是 Hash 索引本身由于其特殊性也带来了很多限制和弊端,主要有以下这些。 (1) Hash 索引仅仅能满足"=",“IN"和”<=>“查询,不能使用范围查询。 由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值,所以它只能用于等值的过滤,不能用于基于范围的过滤,因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系,并不能保证和Hash运算前完全一样。 (2) Hash 索引无法被用来避免数据的排序操作。 由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值,而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样,所以数据库无法利用索引的数据来避免任何排序运算； (3) Hash 索引不能利用部分索引键查询。 对于组合索引,Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值,而不是单独计算 Hash 值,所以通过组合索引的前面一个或几个索引键进行查询的时候,Hash 索引也无法被利用。 (4) Hash 索引在任何时候都不能避免表扫描。 ...

MySQL replace into http://my.oschina.net/junn/blog/110213 Replace into是Insert into的增强版。在向表中插入数据时，我们经常会遇到这样的情况: 1、首先判断数据是否存在；2、如果不存在，则插入；3、如果存在，则更新。 在SQL Server中可以这样处理: if not exists (select 1 from t where id = 1) insert into t(id, update_time) values(1, getdate()) else update t set update_time = getdate() where id = 1 那么 MySQL 中如何实现这样的逻辑呢？MySQL 中有更简单的方法: replace into replace into t(id, update_time) values(1, now()); 或 replace into t(id, update_time) select 1, now(); replace into 跟 insert 功能类似，不同点在于: replace into 首先尝试插入数据到表中， 1. 如果发现表中已经有此行数据 (根据主键或者唯一索引判断) 则先删除此行数据，然后插入新的数据。 2. 否则，直接插入新数据。 ...

MySQL 存储引擎 Memory http://samyubw.blog.51cto.com/978243/223769 Memory存储引擎使用存在内存中的内容来创建表，每个Memory表只实际对应一个磁盘文件，在磁盘中表现为.frm文件。Memory类型的表访问速度非常快，因为它的数据是放在内存中的，并且默认使用hash索引，但是一旦服务关闭，表中的数据就会丢失。 示例: create table memory_tab(id int)engine=memory; 因为memory在MySQLd重启后数据就会丢失，为了获得稳定的数据源，可以在启动MySQLd时加上—init-file选项，把insert into memory_tab select 或load data infile类似的语句放进去即可。 另外因为memory表是把数据放到内存中的，所以服务器需要有足够的内存来维持所有在同一时间使用的memory表，注意对连接MySQLd的所有用户连接 共享 内存表中的数据，根据应用如果不是在所有连接间共享数据，最好在内存表中加入标志各个连接的ID。当不再需要memory数据时，要记得去删除自己连接的数据。 对于内存表的官方说明: The MEMORY storage engine creates tables with contents that are stored in memory. Formerly, these were known as HEAP tables. MEMORY is the preferred term, although HEAP remains supported for backward compatibility. Each MEMORY table is associated with one disk file. The filename begins with the table name and has an extension of .frm to indicate that it stores the table definition. ...

MySQL 存储引擎 什么是存储引擎？ 关系数据库表是用于存储和组织信息的数据结构，可以将表理解为由行和列组成的表格，类似于Excel的电子表格的形式。有的表简单，有的表复杂，有的表根本不用来存储任何长期的数据，有的表读取时非常快，但是插入数据时去很差；而我们在实际开发过程中，就可能需要各种各样的表，不同的表，就意味着存储不同类型的数据，数据的处理上也会存在着差异，那么。对于MySQL来说，它提供了很多种类型的存储引擎，我们可以根据对数据处理的需求，选择不同的存储引擎，从而最大限度的利用MySQL强大的功能。这篇博文将总结和分析各个引擎的特点，以及适用场合，并不会纠结于更深层次的东西。我的学习方法是先学会用，懂得怎么用，再去知道到底是如何能用的。下面就对MySQL支持的存储引擎进行简单的介绍。 MyISAM MyISAM 发音为 “my-z[ei]m”; 在MySQL客户端中，使用以下命令可以查看MySQL支持的引擎:show engines; MyISAM表是独立于操作系统的，这说明可以轻松地将其从Windows服务器移植到Linux服务器；每当我们建立一个MyISAM引擎的表时，就会在本地磁盘上建立三个文件，文件名就是表明。例如，我建立了一个MyISAM引擎的tb_Demo表，那么就会生成以下三个文件: tb_demo.frm，存储表定义； tb_demo.MYD，存储数据； tb_demo.MYI，存储索引。 MyISAM表无法处理事务，这就意味着有事务处理需求的表，不能使用MyISAM存储引擎。MyISAM存储引擎特别适合在以下几种情况下使用: 选择密集型的表。MyISAM存储引擎在筛选大量数据时非常迅速，这是它最突出的优点。 插入密集型的表。MyISAM的并发插入特性允许同时选择和插入数据。例如: MyISAM存储引擎很适合管理邮件或Web服务器日志数据。 InnoDB InnoDB是一个健壮的事务型存储引擎，这种存储引擎已经被很多互联网公司使用，为用户操作非常大的数据存储提供了一个强大的解决方案。我的电脑上安装的MySQL 5.6.13版，InnoDB就是作为默认的存储引擎。InnoDB还引入了行级锁定和外键约束，在以下场合下，使用InnoDB是最理想的选择: 更新密集的表。InnoDB存储引擎特别适合处理多重并发的更新请求。 事务。InnoDB存储引擎是支持事务的标准MySQL存储引擎。 自动灾难恢复。与其它存储引擎不同，InnoDB表能够自动从灾难中恢复。 外键约束。MySQL支持外键的存储引擎只有InnoDB。 支持自动增加列AUTO_INCREMENT属性。 一般来说，如果需要事务支持，并且有较高的并发读取频率，InnoDB是不错的选择。 MEMORY 使用MySQL Memory存储引擎的出发点是速度。为得到最快的响应时间，采用的逻辑存储介质是系统内存。虽然在内存中存储表数据确实会提供很高的性能，但当MySQLd守护进程崩溃时，所有的Memory数据都会丢失。获得速度的同时也带来了一些缺陷。它要求存储在Memory数据表里的数据使用的是长度不变的格式，这意味着不能使用BLOB和TEXT这样的长度可变的数据类型，VARCHAR是一种长度可变的类型，但因为它在MySQL内部当做长度固定不变的CHAR类型，所以可以使用。 一般在以下几种情况下使用Memory存储引擎: 目标数据较小，而且被非常频繁地访问。在内存中存放数据，所以会造成内存的使用，可以通过参数max_heap_table_size控制Memory表的大小，设置此参数，就可以限制Memory表的最大大小。 如果数据是临时的，而且要求必须立即可用，那么就可以存放在内存表中。 存储在Memory表中的数据如果突然丢失，不会对应用服务产生实质的负面影响。 Memory同时支持散列索引和B树索引。B树索引的优于散列索引的是，可以使用部分查询和通配查询，也可以使用<、>和>=等操作符方便数据挖掘。散列索引进行"相等比较"非常快，但是对"范围比较"的速度就慢多了，因此散列索引值适合使用在=和<>的操作符中，不适合在<或>操作符中，也同样不适合用在order by子句中。 可以在表创建时利用USING子句指定要使用的版本。例如: create table users ( id smallint unsigned not null auto_increment, username varchar(15) not null, pwd varchar(15) not null, index using hash (username), primary key (id) )engine=memory; 上述代码创建了一个表，在username字段上使用了HASH散列索引。下面的代码就创建一个表，使用BTREE索引。 create table users ( id smallint unsigned not null auto_increment, ...

MySQL 字典表 select column_key,COLUMN_NAME,column_type,is_nullable,column_comment from INFORMATION_SCHEMA.Columns where table_name=" and table_schema=" 显示数据库列表 MySQL>show databases; -- 说明: 其中字典库是: information_schema，其中常用字典表: INFORMATION_SCHEMA.SCHEMATA -数据库中所有数据库信息 INFORMATION_SCHEMA.TABLES -存放数据库中所有数据库表信息 INFORMATION_SCHEMA.COLUMNS -所有数据库表的列信息 INFORMATION_SCHEMA.STATISTICS -存放索引信息 INFORMATION_SCHEMA.USER_PRIVILEGES - INFORMATION_SCHEMA.SCHEMA_PRIVILEGES INFORMATION_SCHEMA.TABLE_PRIVILEGES INFORMATION_SCHEMA.COLUMN_PRIVILEGES INFORMATION_SCHEMA.CHARACTER_SETS INFORMATION_SCHEMA.COLLATIONS INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY INFORMATION_SCHEMA.TABLE_CONSTRAINTS INFORMATION_SCHEMA.KEY_COLUMN_USAGE -存放数据库里所有具有约束的键信息 INFORMATION_SCHEMA.ROUTINES INFORMATION_SCHEMA.VIEWS -存放所有视图信息 INFORMATION_SCHEMA.TRIGGERS -触发器信息 2、显示当前连接的数据库 MySQL>select database(); 3、显示库中的数据表: MySQL>use MySQL；(指定MySQL库) MySQL>show tables; 4、显示数据表的结构: MySQL>describe yourtablename; 说明: describe 可以简写成desc 5、建库: MySQL>create database yourdbname; 6、建表: MySQL>create table yourtablename (columnname colunmtype, …)； ...

MySQL install debian install MySQL server sudo apt-get install MySQL-server 一、下载MySQL安装包 安装包名称: MySQL-5.6.12-win32.zip 下载地址: http://dev.MySQL.com/downloads/MySQL/ 二、安装MySQL 2.1 修改配置文件my.ini 将MySQL-5.6.12-win32.zip解压拷贝到F:\ 修改F:\MySQL-5.6.12-win32.zip目录下的配置文件my.ini如下: [client] port = 3306 [MySQL] [MySQLd] bind-address = 0.0.0.0 basedir=D:/apps/MySQL-5.6.23-winx64 datadir=D:/apps/MySQL-5.6.23-winx64/data max_connections=200 character-set-server=utf8 port=3306 try to start MySQL with console MySQLd -console 2.2 安装MySQL为windows系统服务 以管理员身份(Run as administrator)启动cmd，切换到目录F:/MySQL-5.6.12-win32/bin MySQLd.exe -install 如果以普通用户执行该命令会提示Install/Remove of the Service Denied! 三、启动MySQL服务 3.1 启动MySQL服务 以管理员身份启动cmd，切换到目录F:/MySQL-5.6.12-win32/bin 启动MySQL服务: net start MySQL 四、执行命令 4.1 登录MySQL MySQL -u root -p ...

设置MySQL允许外网访问 1.修改配置文件 sudo vim /etc/MySQL/my.cnf 把bind-address参数的值改成你的内/外网IP或0.0.0.0,或者直接注释掉这行. 解决MySQL不允许从远程访问的方法 解决方法: 1。改表法。可能是你的帐号不允许从远程登陆，只能在localhost。这个时候只要在localhost的那台电脑，登入MySQL后，更改 “MySQL” 数据库里的 “user” 表里的 “host” 项，从"localhost"改称"%" MySQL -u root -pvmwareMySQL>use MySQL; MySQL>update user set host = ‘%’ where user = ‘root’; MySQL>select host, user from user; MySQL>FLUSH PRIVILEGES 使修改生效.就可以了

MySQL “ON DUPLICATE KEY UPDATE” 如果在INSERT语句末尾指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则在出现重复值的行执行UPDATE；如果不会导致唯一值列重复的问题,则插入新行。 例如,如果列 a 为 主键 或 拥有UNIQUE索引,并且包含值1,则以下两个语句具有相同的效果: INSERT INTO TABLE (a,c) VALUES (1,3) ON DUPLICATE KEY UPDATE c=c+1; UPDATE TABLE SET c=c+1 WHERE a=1; 如果行作为新记录被插入,则受影响行的值显示1；如果原有的记录被更新,则受影响行的值显示2。 这个语法还可以这样用: 如果INSERT多行记录(假设 a 为主键或 a 是一个 UNIQUE索引列): 1.INSERT INTO TABLE (a,c) VALUES (1,3),(1,7) ON DUPLICATE KEY UPDATE c=c+1; 执行后, c 的值会变为 4 (第二条与第一条重复, c 在原值上+1). 2.INSERT INTO TABLE (a,c) VALUES (1,3),(1,7) ON DUPLICATE KEY UPDATE c=VALUES(c); ...

MySQL 日志 log https://mariadb.com/kb/en/mariadb/general-query-log/ 查询日志: 编辑/etc/MySQL/my.cnf, 在[MySQLd]段下添加以下三行并重启MySQL(MySQL用户写权限) general-log=1 general-log-file=/data/logs/MySQL/MySQL.log log-output=file 查看配置: SHOW GLOBAL VARIABLES LIKE '%log%'; MySQL日志: 主要包含: 错误日志、查询日志、慢查询日志、事务日志、二进制日志； 日志是MySQL数据库的重要组成部分。日志文件中记录着MySQL数据库运行期间发生的变化；也就是说用来记录MySQL数据库的客户端连接状况、SQL语句的执行情况和错误信息等。当数据库遭到意外的损坏时，可以通过日志查看文件出错的原因，并且可以通过日志文件进行数据恢复。 错误日志 在MySQL数据库中，错误日志功能是默认开启的。并且，错误日志无法被禁止。默认情况下，错误日志存储在MySQL数据库的数据文件中。错误日志文件通常的名称为hostname.err。其中，hostname表示服务器主机名。 错误日志信息可以自己进行配置的，错误日志所记录的信息是可以通过log-error和log-warnings来定义的，其中log-err是定义是否启用错误日志的功能和错误日志的存储位置，log-warnings是定义是否将警告信息也定义至错误日志中。默认情况下错误日志大概记录以下几个方面的信息: 服务器启动和关闭过程中的信息 (未必是错误信息，如MySQL如何启动InnoDB的表空间文件的、如何初始化自己的存储引擎的等等) 、服务器运行过程中的错误信息、事件调度器运行一个事件时产生的信息、在从服务器上启动服务器进程时产生的信息。 下面我们来定义MySQL错误日志的功能: 一般而言，日志级别的定义没有回话变量都只是在全局级别下进行定义。 MySQL> SHOW GLOBAL VARIABLES LIKE ‘%log%’; +————–+————+ | Variable_name | Value | +————–+————+ | back_log | 50 | | binlog_cache_size | 32768 | | binlog_direct_non_transactional_updates | OFF | | binlog_format | MIXED | | binlog_stmt_cache_size | 32768 | | expire_logs_days | 0 | | general_log | OFF | | general_log_file | /mydata/data/stu18.log | ...

MySQL中的datetime与timestamp 相同 显示 TIMESTAMP列的显示格式与DATETIME列相同。换句话说,显示宽度固定在19字符,并且格式为YYYY-MM-DD HH:MM:SS。 不同 范围 datetime 以’YYYY-MM-DD HH:MM:SS’格式检索和显示DATETIME值。支持的范围为'1000-01-01 00:00:00’到'9999-12-31 23:59:59’TIMESTAMP值不能早于1970或晚于2037 储存 TIMESTAMP 1.4个字节储存 (Time stamp value is stored in 4 bytes) 2.值以UTC格式保存 ( it stores the number of milliseconds) 3.时区转化 ,存储时对当前的时区进行转换,检索时再转换回当前的时区。 datetime 1.8个字节储存 (8 bytes storage) 2.实际格式储存 (Just stores what you have stored and retrieves the same thing which you have stored.) 3.与时区无关 (It has nothing to deal with the TIMEZONE and Conversion.) 实例对比 现在我来做个时区对他们的影响。 1.先插入一个数据insert into `t8` values(now(), now()); ...

在MySQL中修改表名的sql语句 http://blog.csdn.net/xrt95050/article/details/2441458 在使用MySQL时,经常遇到表名不符合规范或标准,但是表里已经有大量的数据了,如何保留数据,只更改表名呢？ 可以通过建一个相同的表结构的表,把原来的数据导入到新表中,但是这样视乎很麻烦。 能否简单使用一个SQL语句就搞定呢？当然可以,MySQL5.0下我们使用这样的SQL语句就可以了。 ALTER TABLE table_name RENAME TO new_table_name 例如 ALTER TABLE admin_user RENAME TO a_user

MySQL的大小写敏感性 lower_case_table_names MySQL的大小写敏感性 lower_case_table_names 在 MySQL 中，数据库和表对就于那些目录下的目录和文件。因而，操作系统的敏感性决定数据库和表命名的大小写敏感。这就意味着数据库和表名在 Windows 中是大小写不敏感的，而在大多数类型的 Unix 系统中是大小写敏感的。 奇怪的是列名与列的别名在所有的情况下均是忽略大小写的，而表的别名又是区分大小写的。 要避免这个问题，你最好在定义数据库命名规则的时候就全部采用小写字母加下划线的组合，而不使用任何的大写字母。 或者也可以强制以 -O lower_case_table_names=1 参数启动 MySQLd (如果使用 -defaults-file=…\my.cnf 参数来读取指定的配置文件启动 MySQLd 的话，你需要在配置文件的 [MySQLd] 区段下增加一行 lower_case_table_names=1) 。这样MySQL 将在创建与查找时将所有的表名自动转换为小写字符 (这个选项缺省地在 Windows 中为 1 ，在 Unix 中为 0。从 MySQL 4.0.2 开始，这个选项同样适用于数据库名) 。 当你更改这个选项时，你必须在启动 MySQLd 前首先将老的表名转换为小写字母。 换句话说，如果你希望在数据库里面创建表的时候保留大小写字符状态，则应该把这个参数置: lower_case_table_names=0 。否则的话你会发现同样的sqldump脚本在不同的操作系统下最终导入的结果不一样 (在Windows下所有的大写字符都变成小写了) 。 值 含义 使用CREATE TABLE或CREATE DATABASE语句指定的大写和小写在硬盘上保存表名和数据库名。名称比较对大小写敏感。在Unix系统中的默认设置即如此。请注意如果在大小写不敏感的文件系统上用-lower-case-table-names=0强制设为0，并且使用不同的大小写访问MyISAM表名，会导致索引破坏。 1 表名在硬盘上以小写保存，名称比较对大小写敏感。MySQL将所有表名转换为小写以便存储和查找。该行为也适合数据库名和表的别名。该值为Windows和Mac OS X系统中的默认值。 2 表名和数据库名在硬盘上使用CREATE TABLE或CREATE DATABASE语句指定的大小写进行保存，但MySQL将它们转换为小写以便查找。名称比较对大小写敏感。注释: 只 在对大小写不敏感的文件系统上适用! InnoDB表名以小写保存，例如lower_case_tables_name=1。 MySQL的大小写敏感其实是根据用户的操作系统来的， 可以强制以 -O lower_case_table_names=1 参数启动 MySQLd (如果使用 -defaults-file=…\my.cnf 参数来读取指定的配置文件启动 MySQLd 的话，你需要在配置文件的 [MySQLd] 区段下增加一行 lower_case_table_names=1) 。 ...

MySQL 索引 - 显示索引信息 SHOW INDEX FROM table_name; SHOW keys FROM table_name; - create index CREATE INDEX indexName ON mytable(username(length)); - 该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL。 ALTER TABLE tbl_name ADD PRIMARY KEY (column_list); - 这条语句创建索引的值必须是唯一的 (除了NULL外,NULL可能会出现多次) 。 ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): - 添加普通索引,索引值可出现多次。 ALTER TABLE tbl_name ADD INDEX index_name (column_list): - 该语句指定了索引为 FULLTEXT ,用于全文索引。 ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list): ALTER TABLE testalter_tbl DROP PRIMARY KEY; ALTER TABLE testalter_tbl DROP INDEX c; SHOW INDEX FROM table_name; ...

innodb_autoinc_lock_mode innodb_autoinc_lock_mode这个参数控制着在向有auto_increment 列的表插入数据时，相关锁的行为； 通过对它的设置可以达到性能与安全(主从的数据一致性)的平衡 【0】我们先对insert做一下分类 首先insert大致上可以分成三类： 1、simple insert 如insert into t(name) values(’test’) 2、bulk insert 如load data | insert into … select …. from …. 3、mixed insert 如insert into t(id,name) values(1,‘a’),(null,‘b’),(5,‘c’); 【1】innodb_autoinc_lock_mode 的说明 innodb_auto_lockmode有三个取值： 1、0 这个表示tradition 传统 2、1 这个表示consecutive 连续 3、2 这个表示interleaved 交错 【1.1】tradition(innodb_autoinc_lock_mode=0) 模式: 1、它提供了一个向后兼容的能力 2、在这一模式下，所有的insert语句(“insert like”) 都要在语句开始的时候得到一个 表级的auto_inc锁，在语句结束的时候才释放这把锁，注意呀，这里说的是语句级而不是事务级的， 一个事务可能包涵有一个或多个语句。 3、它能保证值分配的可预见性，与连续性，可重复性，这个也就保证了insert语句在复制到slave 的时候还能生成和master那边一样的值(它保证了基于语句复制的安全)。 4、由于在这种模式下auto_inc锁一直要保持到语句的结束，所以这个就影响到了并发的插入。 【1.2】consecutive(innodb_autoinc_lock_mode=1) 模式: 1、这一模式下去simple insert 做了优化，由于simple insert一次性插入值的个数可以立马得到 确定，所以mysql可以一次生成几个连续的值，用于这个insert语句；总的来说这个对复制也是安全的 (它保证了基于语句复制的安全) 2、这一模式也是mysql的默认模式，这个模式的好处是auto_inc锁不要一直保持到语句的结束，只要 语句得到了相应的值后就可以提前释放锁 【1.3】interleaved(innodb_autoinc_lock_mode=2) 模式 1、由于这个模式下已经没有了auto_inc锁，所以这个模式下的性能是最好的；但是它也有一个问题，就是 对于同一个语句来说它所得到的auto_incremant值可能不是连续的。 【2】如果你的二进制文件格式是mixed | row 那么这三个值中的任何一个对于你来说都是复制安全的。 ...