4.Mysql相关

最新推荐文章于 2024-02-18 19:30:00 发布

原创最新推荐文章于 2024-02-18 19:30:00 发布 · 390 阅读

0 ·

CC 4.0 BY-SA版权

工作专栏收录该内容

23 篇文章

订阅专栏

本文深入探讨了数据库中各种时间数据类型（如date、time、datetime和timestamp）的使用场景与转换方式，以及bit、bigint和tinyint等数据类型的适用情况。同时，解析了SQL中的常见问题，如update子查询的死锁问题，以及如何锁定数据进行操作。此外，还介绍了MySQL中的时间戳函数和计数查询技巧。

一、数据类型:

1、时间（修改时间、创建时间）

date – > 1000-01-01 to 9999-12-31 ------------javaType： java.sql.Date

mybatis中：jdbcType="DATE"
实体类中：LocalDate类型

time – > 00:00:00到 23:59:59 ------------javaType： java.sql.Time

券不可用时间

datetime --> 1000-01-01 00:00:00 to 9999-12-31 23:59:59 -------------javaType：java.sql.Timestamp

mybatis中：jdbcType="TIMESTAMP"
实体类中：LocalDateTime类型
券生效日期，失效日期，截止日期

timestamp(时间戳) -->1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07-------------javaType：java.sql.Timestamp
注：

mybatis中：jdbcType="TIMESTAMP"
实体类中：LocalDateTime类型
只占4字节，而datetime占8字节
在关心创建时间和更新时间的情况下，建议将更新时间设置为时间戳字段。
时间戳会自动转换时区
如果存进去的是NULL，timestamp会自动储存当前时间，而 datetime会储存 NULL。

2、bit—（是否已经删除）

mybatis中：jdbcType="BIT"
实体类中：boolean类型

利用它可定义一个位变量，但不能定义位指针，也不能定义位数组。它的值是一个二进制位，不是0就是1，类似Boolean类型中的True和False。

插入
插入数据时，插入true,false，数据库会相应的转换成1，0。

更新
更新最常用的办法肯定是先查询判断再进行更新,这样就需要先查询一次,再执行更新.
如果不想进行查询，直接对它进行更新，每次得到更新前的相反值的话，可以使用一下方法：
1）、substr函数

update anketo1 set fb = substring(‘10’,fb+1,1) WHERE id =5

2）、直接取反

update anketo1 set fb = ~ fb WHERE id =5

3）、用ABS取绝对值

update anketo1 set fb = abs(fb-1)

查询
查询数据时，得到的数据是0和1，而不是true或false。

3、bigint—(订单金额，满减金额,操作记录的自增id)

mybatis中：jdbcType="long"
实体类中：long类型

从 -2^63 (-9223372036854775808) 到 2^63-1 (9223372036854775807) 的整型数据（所有数字）。存储大小为 8 个字节。
在支持整数值的地方支持 bigint 数据类型。但是，bigint 用于某些特殊的情况，当整数值超过 int 数据类型支持的范围时，就可以采用 bigint。

4、tinyint—(操作类型，记录类型，不同的数字代表不同的类型)

从 0 到 255 的整型数据。存储大小为 1 字节。

mybatis中：jdbcType="TINYINT"
实体类中：short类型

二、SQL问题

1.问题： update中的子查询会加共享锁，引起死锁问题

例如：

update a set a.c=a.c-1 where (select q.cc from (select a.c cc from a) q )>1

原因：在默认的事务隔离级别下，即REPEATABLE READ下，InnoDB存储引擎采用Next-Key Locking机制（Gap Lock间隙锁+Record Lock行锁）来避免Phantom Problem（幻象问题）
行锁的三种算法

补充:
如何锁住一行数据或整张表进行操作?(使用for update)
在这里插入图片描述
当for update的字段为索引或者主键的时候，只会锁住索引或者主键对应的行。
当for update的字段为普通字段的时候，Innodb会锁住整张表。

2.mysqsl中的函数

2.1unix_timestamp函数：返回一个确切时间点的时间戳（有两种重载形式）

① unix_timestamp()，返回自1970-1-1 8:00:00开始到当前系统时间为止的秒数。
②unix_timestamp(date)，返回1970-1-1 8:00:00开始到date所代表的时间为止的秒数，对于早于1970-1-1 8:00:00的时间，总是返回 0 。
在这里插入图片描述

2.2from_unixtime(unixtime) 根据时间戳返回一个确切的时间
在这里插入图片描述

3.查询表中所有数据条数

SELECT COUNT(1) FROM `表名`

4.in 和 not in

not in

错误的使用方法!!! 原因:子查询中不能有null,如果有null,则结果必为0

SELECT COUNT(name) FROM CVE WHERE name NOT IN ('CVE-1999-0001', 'CVE-1999-0002', NULL);

正确的方法是:

SELECT COUNT(DISTINCT name)

FROM CVE

WHERE name NOT IN (SELECT cveID FROM cve_sig WHERE cveID IS NOT NULL)

in后面的结果集有null,不影响!!!

SELECT COUNT(DISTINCT name)

FROM CVE

WHERE name IN (SELECT cveID FROM cve_sig WHERE cveID IS NOT NULL)