MySQL 转 PostgreSQL (holo)

语法区别

3. 关于日期、时间的提取

timestamp 类型的数据,把小时、分钟等提取出来。

--mysql
    SELECT hour(time) AS timeHour
    FROM   cart
    WHERE  ......;

--PostgreSQL (holo) 方法一
    SELECT DATE_PART('hour', time) as timeHour
    FROM   cart
    WHERE  ......;

--PostgreSQL (holo) 方法二
    SELECT EXTRACT(hour FROM time) AS timeHour
    FROM   cart
    WHERE  ......;

其中DATE_PART(field, source)extract() 两者等效,第一个参数可选以下内容:
* century 世纪
* decade 年份 ÷ 10
* year
* month 对于timestamp值,它是一年里的月份数(1-12);对于interval值, 它是月的数目,然后对 12 取模(0-11)
* day
* hour 小时
* minute 分钟
* second
* microseconds 秒域(包括小数部分)乘以 1,000,000 。请注意它包括全部的秒。
* milliseconds 秒域(包括小数部分)乘以 1,000 。请注意它包括完整的秒。
* dow 每周的星期几 , 星期日(0),星期六(1)
* doy 一年的第 x 天
* epoch
* isodow
* isoyear
* timezone
* timezone_hour
* timezone_minute

(参考资料 / 详细资料 连接)

GROUP BY

将MySQL中包含GROUP BY的代码,直接迁移到PGSQL中,有概率会遇到这个问题:
[42803] ERROR: column "table.colName" must appear in the GROUP BY clause or be used in an aggregate function

错误:”cart.id”列 必须出现在GROUP BY子句中或在聚合函数中使用

数据库的三范式

范式概念

在关系型数据库中,设计数据库时要考虑遵循一定的规则,这种规则就是范式。

实际上,数据库一共有六种范式,但一般设计数据库的时候,只需要遵守到3NF(第三范式)即可。
1NF 2NF 3NF BCNF 4NF 5NF

第一范式 1NF

第一范式主要强调原子性,即数据库的每一列都是一个不可再分割的基本数据项。

例:下表不满足第一范式

学号 姓名 家庭电话及住址
23333 马猴烧酒 166 xxxx xxxx,唐辛子星

修改后即可满足第一范式

学号 姓名 家庭电话 住址
23333 马猴烧酒 166 xxxx xxxx 唐辛子星

第二范式 2NF

第二范式主要强调唯一性,即数据表中每行都必须可以被唯一地区分,主要是为了防止数据重复,通常是使用一个主键来唯一标识一条记录。
满足第二范式就肯定满足第一范式。

  • 完全依赖、部分依赖
    因为主键可以由多列共同组成,
    当且仅当该行数据由主键中所有列共同确定时,我们说这条数据完全依赖于主键。
    当该行数据仅需要主键中的部分列即可确定时,我们说这条数据部分依赖于主键。

:完全依赖

学号(主键) 科目(主键) 分数
23333 数学 99

分数完全依赖于学号和科目,二者缺一不可。

:部分依赖

学号(主键) 姓名(主键) 科目(主键) 分数
23333 张三 数据库原理及应用 88

分数部分依赖于上述三个主键,原因:学号就可以确定出某位学生的姓名,分数依赖于科目和(学号姓名)在没有重名的情况下

第三范式 3NF

非主属性之间不能相互依赖,必须直接依赖候选关键字。
例:下表不满足第三范式

学号(主键) 姓名 班级 学生电话 班主任姓名 班主任电话
23333 张三 985211 168 xxxx xxxx 李四 189 xxxx xxxx
23334 王五 985211 199 xxxx xxxx 李四 189 xxxx xxxx

班主任的姓名、班主任的电话跟主键学号没有直接的关系,只有间接的关系(可以通过学生的班级了解到班主任的姓名电话)。故该表的非主属性班主任姓名、班主任电话依赖该表的非主属性班级,且间接依赖关键字学号。

修改后满足第三范式:

学号(主键) 姓名 班级 学生电话
23333 张三 985211 168 xxxx xxxx
23334 王五 985211 199 xxxx xxxx
班级 班主任姓名 班主任电话
985211 李四 189 xxxx xxxx
  • 直接依赖、传递依赖(函数依赖、间接依赖)
    a依赖b,b依赖c,则a肯定依赖c,这就是传递依赖。

学号 姓名 班级 班主任姓名
23333 张三 985211 李四

班主任姓名依赖于班级,班级依赖于学号,所以班主任姓名传递依赖学号,直接依赖班级。

MySQL之事务与隔离级别(概要篇)

事务

事务的概念

事务就是一组原子性的SQL查询,或者说是一个独立的工作单元。如果数据库引擎能够成功地对数据库应用该组查询的全部语句,那么久执行该组查询。如果其中有任何一条语句因为崩溃或者其他原因无法执行,那么所有的语句都将不会执行。
也就是说,事务内的语句,要么全部执行成功,要么全部执行失败。

经典例子:“银行应用”

假设要从用户A的银行卡账户转到用户B的银行卡账户200元钱,应该怎么做?

应该至少需要三个步骤:
1. 检查用户A的账户余额是否高于200元?
1. 从用户A的银行卡账户中减去200元。
1. 在用户B的银行卡账户中加上200元。

上述三个步骤的操作必须打包在一个事务中,任何一个步骤失败,则必须回滚所有的步骤。

可以用 START TRANSACTION 语句开始一个事务,然后要么使用 CONNIT 提交事务将修改的数据并持久保留,要么使用 ROLLBACK 撤销所有的修改。

事务的SQL样本如下:

START TRANSACTION;
SELECT balance FROM savings WHERE customer_ id = 111;
UPDATE savings balance = balance - 200. 00 WHERE customer_ id = 111;
UPDATE savings SET balance = balance + 200. 00 WHERE customer_ id = 111;
COMMIT;

试想一下,如果执行到第4条语句的时候,服务器奔溃了,会发生什么?
也许用户A因此损失了200元。也许执行到第3到第4条语句之间,服务器奔溃了,用户B因此白得到了200元。

除非系统通过严格的ACID测试,否则空谈事务的概念是远远不够的。

什么是ACID?

ACID表示 原子性(atomicity)一致性(consistency)隔离性(isolation)持久性(durability) 。一个运行良好的事务处理系统,必须具备这些标准的特征。

  • 原子性(atomicity):一个事务必须被视为一个不可分割的最小工作单元,整个事务中的所有操作要么全部提交成功,要么全部失败回滚,对于一个事务来说,不可能只执行其中的一个部分操作,这就是事务的原子性。

  • 一致性(consistency):数据库总是从一个一致性的状态转换到另外一个一致性的状态。在前面的例子中,一致性确保了,即使在执行第三、四条语句之间时系统崩溃,A账户中也不会损失200美元,因为事务最终没有提交,所以事务中所做的修改也不会保存到数据库中。

  • 隔离性(isolation):通常来说,一个事务所做的修改在最终提交以前,对其他事务是不可见的。在前面的例子中,当执行完第三条语句、第四条语句还未开始时,此时有另外一个关于A账户付款程序开始运行,则其看到的A账户的余额并没有被减去200美元。

  • 持久性(durability):一旦事务提交,则其所做的修改就会永久保存到数据库中。此时即使系统崩溃,修改的数据也不会丢失。

在实际应用中,若想实现ACID特效非常地难,甚至可以说是一个不可能完成的任务。

就像各种锁一样,实现ACID的数据库相比没有实现的数据库,需要花费更多的CPU时间。

事务的隔离级别

MySQL的并发控制(概要篇)

并发控制

无论何时,只要有多个查询需要在同一时刻修改数据,都会产生并发控制问题。

如果一个用户正在读取一张表,而另一位用户正在试图删除这张表中第25行,会产生什么结果?结论是不确定的,读取的用户可能会因此报错退出,也有可能读到了信息不一致的表。

如何解决这类问题呢?

在处理并发读或者写时,可以通过实现一个由两种类型的锁组成的锁系统来解决问题。这两种类型的锁通常被称为 共享锁(shared lock)排他锁(exclusive lock),也叫 读锁(read lock)写锁(write lock)

  • 读锁:读锁是共享的,是相互不阻塞的,多个客户在同一时刻可以同时读取同一个资源,而不互相干扰。
  • 写锁:写锁是排他的,一个写锁会阻塞其他写锁和读锁,只有这样,才能确保在同一时间里只有一个用户能执行写入操作,并防止其他用户读取正在写入的同一资源。
    写锁比读锁有更高的优先级,因此一个写锁请求可能会被插入到读锁队列的前面(写锁可以插入到锁队列中读锁的前面,反之读锁则不能插入到写锁的前面)。

锁粒度

加锁、获得锁、检查锁、释放锁等都会增加系统的开销,且在任何时候,在给定的资源商,锁定的数据越少,则系统的并发程度越高。
由上,我们应该寻找到合适的锁策略,即在锁的开销和数据的安全性之间寻求平衡,以达到最高的性能。
在MySQL中,两种最重要的锁策略是 表锁(Table lock)行级锁(row lock)

  • 表锁:表锁是MySQL中最基本的锁策略,并且是开销最小的策略。尽管存储引擎可以管理自己的锁,但是MySQL本身还是会使用各种有效的锁来实现不同目的。
  • 行级锁:行级锁可以最大程度地支持并发处理(同时也带来了最大的锁开销)。行级锁只在存储引擎层实现,而MySQL服务器层没有实现。

MySQL逻辑结构

MySQL逻辑架构

第一层:服务不是MySQL独有的,大多数基于网络和客户端/服务器的工具或者服务都有类似的架构。每个客户端连接都会在服务器进程中拥有一个线程。

第二层:大多数MySQL核心服务功能都在这一层,包括查询解析分析优化缓存以及所有的内置函数(例如时间、日期、数学和加密函数等),所有跨存储引擎的功能都在这一层实现(例如存储过程、触发器、视图等)。

第三层:存储引擎。其负责MYSQL中数据的存储和提取。服务器通过API与存储引擎进行通信。存储引擎不会去解析SQL,不同存储引擎之间也不会相互通信,而只是简单地响应上层服务器的请求。