常见的三个经典的MySQL问题

爱被打了一巴掌 2024-03-31 09:49 84阅读 0赞

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

*  一、在MySQL中INNER JOIN、LEFT JOIN、RIGHT JOIN 和 FULL JOIN 有什么区别？
 *  二、你会推荐使用 datetime 还是 timestamp 字段？为什么？
 *  三、MyISAM 与 InnoDB，什么场景选择哪一个？

一、在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  kerala
    2   24  usa
    3   25  ekm
    5   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  kerala
    ann             antony          24  usa
    sruthy          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    kerala
    ann                      antony                   24    usa
    sruthy                   abc                      25    ekm
    new                      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     kerala
    ann                      antony                 24     usa
    sruthy                   abc                    25     ekm
    NULL                     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    kerala
    ann                   antony                24    usa
    sruthy                abc                   25    ekm
    new                   abc                   NULL  NULL
    NULL                  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。