写点什么

三个经典的 MySQL 问题

作者:千锋IT教育
  • 2022-11-21
    北京
  • 本文字数:3710 字

    阅读完需:约 12 分钟

大家好,今天给大家上 3 个经典的 MySQL 问题,希望能对大家有帮助!但是因为笔者计算机水平有限,可能会存在一些错误,烦请指出、斧正!谢谢!

一、在 MySQL 中 INNER JOIN、LEFT JOIN、RIGHT JOIN 和 FULL JOIN 有什么区别?

我们有两张表:

TableA:id  firstName                  lastName.......................................1   arun                        prasanth                 2   ann                         antony                   3   sruthy                      abc                      6   new                         abc                                           复制代码
复制代码


TableB:id2 age Place................1   24  kerala2   24  usa3   25  ekm5   24  chennai复制代码
复制代码

1. INNER JOIN(内连接)

这是最简单,最常见,也是最容易理解的 Join,两张表使用内连接查询时,得到的结果是两张表中完全匹配的行集。

对于上述两张表,我们有:

SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place  FROM TableA INNER JOIN TableB    ON TableA.id = TableB.id2;复制代码
复制代码

得到的结果即为:

firstName       lastName       age  Place..............................................arun            prasanth        24  keralaann             antony          24  usasruthy          abc             25  ekm复制代码
复制代码

得到的结果有 4 个字段,firstName 、 lastName 、 age 、 Place,就是我们上面 SQL 语句 SELECT 的 4 个字段,FROM 和 INNER JOIN 后面的两个表名就是要内连接的两张表,ON 后面就是在其中寻找共同点的字段。

2. LEFT JOIN(左连接)

左连接查询会返回左表中所有行,无论这些行是不是有任何一行在右表中匹配。

SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place  FROM TableA  LEFT JOIN TableB    ON TableA.id = TableB.id2;复制代码
复制代码

查询结果是:

firstName                lastName                 age   Place.............................................................arun                     prasanth                 24    keralaann                      antony                   24    usasruthy                   abc                      25    ekmnew                      abc                      NULL  NULL复制代码
复制代码

我们可以看到,TableA 中所有行都过来了,即使 firstName 为 new,lastName 为 abc 的那一行 id 为 6,在 TableB 中找不到 id 为 6 的行,仍然在结果集中存在。值得注意的是,因为其 id 为 6,在 TableB 中找不到对应的 id,因此其没有 age 和 Place 字段的内容。

3. RIGHT JOIN(右连接)

右连接查询会返回右表中所有行,无论这些行是不是有任何一行在左表中匹配。

SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place  FROM TableA RIGHT JOIN TableB    ON TableA.id = TableB.id2;复制代码
复制代码

结果集:

firstName                lastName               age     Place...............................................................arun                     prasanth               24     keralaann                      antony                 24     usasruthy                   abc                    25     ekmNULL                     NULL                   24     chennai复制代码
复制代码

4. FULL JOIN(全连接)

SQL 语句如下:

SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place  FROM TableA  FULL JOIN TableB    ON TableA.id = TableB.id2;复制代码
复制代码

结果集为:

firstName             lastName              age    Place...........................................................arun                  prasanth              24    keralaann                   antony                24    usasruthy                abc                   25    ekmnew                   abc                   NULL  NULLNULL                  NULL                  24    chennai复制代码
复制代码

查询结果是左连接和右连接的并集。

二、你会推荐使用 datetime 还是 timestamp 字段?为什么?

  1. 正如 Mysql 文档描述的那样——datetime 的范围是“1000-01-01 00:00:00”到“9999-12-31 23:59:59”,而 timestamp 的范围是 '1970-01-01 00:00:01' UTC 到 '2038-01-09 03:14:07' UTC。如果不想程序在 2038 年出问题,从这个方面作者建议还是使用 datetime。

  2. 有一种观点是既不使用 DATETIME 也不使用 TIMESTAMP 字段。如果想将特定的一天作为一个整体来表示(例如生日),可以使用 DATE 类型,但是如果需要更具体的时间,不要使用 DATETIME 或 TIMESTAMP,而是使用 BIGINT,并简单地存储自纪元以来的毫秒数(如果使用的是 Java,则为 System.currentTimeMillis())。

    这样有几个优点。其一,可以在迁移数据库时避免因为数据类型差异,比如 MySQL 的 DATETIME 类型和 Oracle 的 DATETIME 类型之间可能存在差异,timestamp 类型的精度可能也存在差异,MySQL 的 timestamp 精度不是一开始就支持毫秒精度的。其二,没有时区问题,无论是哪个时区,因为开始计算的时间不同,无论当前时间如何,跨度是一致的。其三,没有 timestamp 和 datatime 的范围问题,哪怕是 datatime,8000 年以后也不能用了,没准我的数据库 8000 年能用 8000 年呢。

  3. 需要注意的是,bigint 是占用 8 个字节,而 timestamp 只占用 4 个字节。从 MySQL 5.6.4 开始,Datetime 的存储空间变成了 5 个字节了(准确的说应该是 5 字节+0~3 个字节的 FSP 分秒精度)。

三、MyISAM 与 InnoDB,什么场景选择哪一个?

MyISAM 和 InnoDB 之间的主要区别在于参照完整性和事务。还有其他区别,例如锁定、回滚和全文搜索。

参照完整性

参照完整性确保表之间的关系保持一致。更具体地说,当一个表(例如 Listings)有一个外键(例如 Product ID)指向另一个表(例如 Products)时,当指向的表发生更新或删除时,这些更改会级联到链接的表。在该示例中,如果重命名产品,则链接的表的外键也会更新;如果从 Products 表中删除产品,则指向已删除条目的 Listings 表中得到任何列表也将被删除。此外,任何 Listings 表中的新列表都必须具有指向有效的现有条目的外键。

InnoDB 是一个关系型 DBMS (RDBMS),因此具有参照完整性,而 MyISAM 则没有。

事务和原子性

使用数据操作语言 (DML) 语句管理表中的数据,例如 SELECT、INSERT、UPDATE 和 DELETE。事务将两个或多个 DML 语句组合成一个工作单元,因此要么应用整个单元,要么不应用。

MyISAM 不支持事务,而 InnoDB 支持。

如果在使用 MyISAM 表时操作被中断,该操作将立即中止,并且受影响的行(甚至每行中的数据)仍然受到影响,即使该操作没有完成。

如果一个操作在使用 InnoDB 表时被中断,因为它使用具有原子性的事务,任何没有完成的事务都不会生效,因为没有提交。

表锁定与行锁定

当查询 MyISAM 表时,正在查询的整个表将被锁定。这意味着后续查询将仅在当前查询完成后才能执行。如果您正在读取一个大表,并且有频繁的读写操作,这可能意味着大量的查询积压。

而当查询 InnoDB 表时,只有涉及的行被锁定,表的其余部分仍然可进行 CRUD 操作。这意味着查询可以在同一个表上同时运行,只要它们不使用同一行。

InnoDB 中的此功能称为并发。尽管并发性很好,但在表的范围查询时有一个缺点,因为在内核线程之间切换存在开销,我们应该对内核线程设置限制以防止服务器停止。

事务和回滚

当在 MyISAM 中执行一个操作时,更改会立刻生效;在 InnoDB 中,这些更改可以回滚。用于控制事务的最常用命令是 COMMIT、ROLLBACK 和 SAVEPOINT。

  1. COMMIT - 您可以编写多个 DML 操作,但只有在进行 COMMIT 时才会保存更改

  2. ROLLBACK - 您可以丢弃任何尚未提交的操作

  3. SAVEPOINT - 实现回滚到指定保存点

可靠性

MyISAM 不提供数据完整性——硬件故障、不正常的关机操作都可能导致数据损坏。这将需要修复或重建索引和表。

而 InnoDB 使用事务日志、双写缓冲区和自动校验和和验证来防止数据损坏。在 InnoDB 进行任何更改之前,它会将事务之前的数据记录到一个名为 ibdata1 的系统表空间文件中。如果发生崩溃,InnoDB 将通过这些日志来自动恢复。

全文索引

InnoDB 在 MySQL 5.6.4 版本之前不支持 FULLTEXT 索引。

但是,这不是使用 MyISAM 的理由。最好使用最新版本的 MySQL 。并不是说使用 FULLTEXT 索引的 MyISAM 表不能转换为 InnoDB 表。

结论

总之,InnoDB 应该是我们默认的存储引擎。在有特定需求时可以选择 MyISAM 或其他数据类型。

用户头像

国内IT培训机构良心品牌 2022-08-02 加入

学习资料下载获取,添加QQ:3547925594

评论

发布
暂无评论
三个经典的MySQL问题_千锋IT教育_InfoQ写作社区