MySQL 中 datetime 和 timestamp 的区别与选择

MySQL 中常用的两种时间储存类型分别是datetime和 timestamp。如何在它们之间选择是建表时必要的考虑。下面就谈谈他们的区别和怎么选择。 1 区别 1.1 占用空间 类型 占据字节 表示形式 datetime 8 字节 yyyy-mm-dd hh:mm:ss timestamp 4 字节 yyyy-mm-dd hh:mm:ss 1.2 表示范围 类型 表示范围 datetime '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999' timestamp '1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999' timestamp翻译为汉语即"时间戳",它是当前时间到 Unix元年(1970 年 1 月 1 日 0 时 0 分 0 秒)的秒数。对于某些时间的计算,如果是以 datetime 的形式会比较困难,假如我是 1994-1-20 06:0 […]

MySQL触发器更新本表数据异常:Can’t update table 'tbl' in stored function/trigger because it is already used by statement which invoked this

如果你在触发器里面对刚刚插入的数据进行了 insert/update, 则出现这个问题。因为会造成循环的调用.   create trigger test before update on test for each row update test set NEW.updateTime = NOW() where id=NEW.ID; END 应该使用set操作,而不是在触发器里使用 update,比如 create trigger test before update on test for each row set NEW.updateTime = NOW(); END ———————————————— 版权声明:本文为CSDN博主「老紫竹」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。 原文链接:https://blog.csdn.net/java2000_net/article/details/3857579

MySQL中的JSON类型

前言(废话) 昨天抽了点时间在网上搜列了一个开源项目,项目挺完整的,前后台分离还带有微信小程序,我Clone下代码,经过一番倒腾,嘿~还真就跑起来了。在这个过程中,体验了一把VUE项目工程细节,因为之前没有接触过这一块,所以还是花费了点时间,好在开源项目的QQ群里楼主帮忙看了一下,才得以顺利往后进行。后来也有很多网友向楼主提问,也抛出了一些问题,其中有个问题到引起了我的注意。 有个小伙伴执行SQL的时候报错了,以为项目中给的SQL脚本不全,但是在群里他并没有给出报错细节的截图,楼主后来也就提示了他一句MySQL版本需要在5.7以上,但是后面就没有更多消息了。 今天早上我还在上班路上,群里的小伙伴就私信我,说能否帮他看下数据库的问题。等我到了公司再回复他的时候,他告诉我说数据库问题已经解决了,我追问了一下细节,原来是开源项目中的数据库建表语句中包含JSON类型字段,导致了他批量执行SQL脚本不成功。其实这样的问题,在执行脚本的时候遇到错误是有日志的,详细看下日志应该明了。 我其实是没有注意到这个细节的,因为我前天安装的数据库就直接上了8.0,屏蔽了这个问题,但是,MySQL数据库现在支持 […]

MySQL 查询优化

索引 索引是什么 MySQL 官方对索引的定义为:索引(Index)是帮助 MySQL 高效获取数据的数据结构。可以得到索引的本质:索引是数据结构。 可以简单理解为排好序的快速查找数据结构。 在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。 一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储的磁盘上。 优缺点 优点 提高数据检索的效率,降低数据库的 IO 成本。 通过索引列对数据进行排序,降低数据排序的成本,降低了 CPU 的消耗。 缺点 虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行 INSERT、UPDATE 和 DELETE。因为更新表时,MySQL 不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为 更新所带来的键值变化后的索引信息。 实际上索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也是要占用空间 的。 B-Tree 和 B+Tree 区别 B-Tree 的关 […]

5分钟学会MySQL-this is incompatible with sql_mode=only_full_group_by错误解决方案

   5分钟学会MySQL-           "this is incompatible with sql_mode=only_full_group_by"错误解决方案   前言:        一、原理层面 这个错误发生在mysql 5.7 版本及以上版本会出现的问题: mysql 5.7版本默认的sql配置是:sql_mode="ONLY_FULL_GROUP_BY",这个配置严格执行了"SQL92标准"。 很多从5.6升级到5.7时,为了语法兼容,大部分都会选择调整sql_mode,使其保持跟5.6一致,为了尽量兼容程序。   二、sql层面 在sql执行时,出现该原因: 简单来说就是:输出的结果是叫target list,就是select后面跟着的字段,还有一个地方group by column,就是 group by后面跟着的字段。由于开启了ONLY_FULL_GROUP_BY的设置,所以如果一个字段没有在target list 和group by字段中同时出现,或者是聚合函数的值的话,那么这 […]

MySQL的JOIN(一):用法

JOIN的含义就如英文单词“join”一样,连接两张表,大致分为内连接,外连接,右连接,左连接,自然连接。这里描述先甩出一张用烂了的图,然后插入测试数据。  

笛卡尔积:CROSS JOIN 要理解各种JOIN首先要理解笛卡尔积。笛卡尔积就是将A表的每一条记录与B表的每一条记录强行拼在一起。所以,如果A表有n条记录,B表有m条记录,笛卡尔积产生的结果就会产生n*m条记录。下面的例子,t_blog有10条记录,t_type有5条记录,所有他们俩的笛卡尔积有50条记录。有五种产生笛卡尔积的方式如下。

内连接:INNER JOIN 内连接INNER JOIN是最常用的连接操作。从数学的角度讲就是求两个表的交集,从笛卡尔积的角度讲就是从笛卡尔积中挑出ON子句条件成立的记录。有INNER JOIN,WHERE(等值连接),STRAIGHT_JOIN,JOIN(省略INNER)四种写法。至于哪种好我会在MySQL的JOIN(二):优化讲述。示例如下。 [cr […]

MYSQL解决 The total number of locks exceeds the lock table size 问题

当我们对Mysql 中的表进行大批量的分组,查询、 创建的时候经常会出现这个问题,他翻译过来大概是这个意思,总数已经超过锁定表的大小。一般出现这个原因会有两种可能: 1.MySQL的临时表过小也就是tmp_table_size tmp_table_size:它规定了内部内存临时表的最大值,每个线程都要分配。(实际起限制作用的是tmp_table_size和max_heap_table_size的最小值。)如果内存临时表超出了限制,MySQL就会自动地把它转化为基于磁盘的MyISAM表,存储在指定的tmpdir目录下,默认如下图: 那么怎么改变临时表的大小呢?同样有两种方法 第一种是 使用命令行进行修改 先使用 show variables like "%tmp%";查看临时表的大小,我的是64M 然后使用SET GLOBAL  tmp_table_size =1024*1024*1024;我是给了1个G 第一种重启电脑后默认的设置会还原,适合临时使用一次的用户。或者我没有找到怎么设置永久的方法,如果有小伙伴们发现请告诉我哦, 第二种是修改mysql 的配置文件 1. […]

Windows批处理执行sql脚本/语句

脚本 SET dbhost=127.0.0.1 SET dbuser=root SET dbpasswd=xxxx SET dbName=dbnamexx SET sqlpath=./ set sqlfile=statistics_fee_data_sync.sql mysql -h%dbhost% -u%dbuser% -p%dbpasswd% --database=%dbName% < %sqlpath%%sqlfile% --default-character-set=utf8 @ECHO Done! ========================================================================================= 语句 SET dbhost=127.0.0.1 SET dbuser=root SET dbpasswd=xxxx SET dbName=dbnamexx SET sqlpath=./ set sqlfile=statistics_fee_data_sync.sql […]

MySQL定时执行脚本(计划任务)命令实例

在mysql中我们可以直接进行一些参数设置让它成定时为我们执行一些任务了,这个虽然可以使用windows或者linux中的计划任务实现,但是mysql本身也能完成 查看event是否开启 复制代码 代码如下: show variables like '%sche%'; 将事件计划开启 复制代码 代码如下: set global event_scheduler =1;   创建存储过程test   复制代码 代码如下: CREATE PROCEDURE test () BEGIN update examinfo SET endtime = now() WHERE id = 14; END; 创建event e_test 复制代码 代码如下: create event if not exists e_test on schedule every 30 second on completion preserve do call test(); 每隔30秒将执行存储过程test,将当前时间更新到examinfo表中id=14的记录的endtime字段中去 […]

mysql实现动态行转列

需求背景:在任务管理系统中,有任务详情表,每个任务下又分子任务节点,每个任务节点都有具体的跟进日期,包括开始时间结束时间,每天的任务完成进度。 有这样一个需求:在任务管理系统中根据任务节点id,展示每天的进度情况。 因为每个任务的时间范围不一样,所以需要用到动态的行转列,将时间周期作为动态的列,进度做为列的值。 任务节点表: quest_node_id:任务子节点id,follow_date:跟进日期 ,grade:进度百分比。 在mysql中创建存储过程:

  注意:我发现follow_date的日期格式(如2018-08-10)会报错,存储过程执行不了,并且数字开头也不可以,所以我把日期改成了varchar类型,并在值里加了前缀。 执行结果如下: 某个节点在某天没有值则补零。 参考文档:mysql 动态行转列   from:https://blog.csdn.net/xiaoxiangzi520/article/details/81480477

mysql行列转换

创建数据库、表

  添加数据

  方式一:使用if

  方式二:使用case

  方法三: with rollup

from:https://www.cnblogs.com/weibanggang/p/9679301.html

MYSQL 数据库分区及取消分区

同事MM之前分享了数据库分区的知识,之前没认真研究过,结果现在自己的数据库资料越来越多,让强迫症的我特别难受,所以就对着同事写的SOP一步一步操作 。这里只介绍Range分区,因为我也是刚用到这一个 . 举个例子:要求将数据库dbemployeeinfo_copy 按ID分区,<3的为一个区,其它的为另一个区.,之后取消分区 原Table 的ID栏位为int格式: 实际生成的table在文件中的存储格式: 具体步骤如下: 1. 进入该table设置页,点击Partioning进行分区: 2.查看C盘该table位置,会发现原来的table已经分成两部分了,之后再增加资料,Part0的大小都不会发生变化了。   有时候会发现当初没考虑好,所以分区分错了,需要重新设置,所以第一反应就是把之前在Partitioning里设置的全部取消,然后提交就好了,但是会发现时无法提交的 这时候只能用指令解决了:alter table test.dbemployeeinfo_copy remove partitioning; 再去检查一下C盘对应文件夹里该table的资料,就没有分区了 & […]