Dinesh Kumar's questions

在mysql 5.6中，考虑这两个在 A、B、C 和 D 之间创建关系的示例。

示例 1

CREATE TABLE `a` (
    id INT UNSIGNED NOT NULL,
    PRIMARY KEY (id)
) ENGINE = INNODB;

CREATE TABLE `b` (
    id INT UNSIGNED NOT NULL,
    a INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (a) REFERENCES a (id) ON DELETE CASCADE
) ENGINE = INNODB;

CREATE TABLE `c` (
    id INT UNSIGNED NOT NULL,
    a INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (a) REFERENCES a (id) ON DELETE CASCADE
) ENGINE = INNODB;

CREATE TABLE `d` (
    id INT UNSIGNED NOT NULL,
    b INT UNSIGNED NOT NULL,
    c INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (id) REFERENCES b (id) ON DELETE CASCADE,
    FOREIGN KEY (id) REFERENCES c (id) ON DELETE RESTRICT
) ENGINE = INNODB;

INSERT INTO a VALUES (1);
INSERT INTO b VALUES (1, 1);
INSERT INTO c VALUES (1, 1);
INSERT INTO d VALUES (1, 1, 1);

DELETE FROM a;

结果是所有行都被删除。

示例 2

CREATE TABLE `a` (
    id INT UNSIGNED NOT NULL,
    PRIMARY KEY (id)
) ENGINE = INNODB;

CREATE TABLE `b` (
    id INT UNSIGNED NOT NULL,
    a INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (a) REFERENCES a (id) ON DELETE CASCADE
) ENGINE = INNODB;

CREATE TABLE `c` (
    id INT UNSIGNED NOT NULL,
    a INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (a) REFERENCES a (id) ON DELETE CASCADE
) ENGINE = INNODB;

CREATE TABLE `d` (
    id INT UNSIGNED NOT NULL,
    b INT UNSIGNED NOT NULL,
    c INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (id) REFERENCES b (id) ON DELETE RESTRICT,
    FOREIGN KEY (id) REFERENCES c (id) ON DELETE CASCADE
) ENGINE = INNODB;

INSERT INTO a VALUES (1);
INSERT INTO b VALUES (1, 1);
INSERT INTO c VALUES (1, 1);
INSERT INTO d VALUES (1, 1, 1);

DELETE FROM a;

请注意，唯一的区别是d的外键是RESTRICT的变化。然而，这个例子失败了

错误代码：1451 无法删除或更新父行：外键约束失败（hello. d，CONSTRAINT d_ibfk_1FOREIGN KEY（id）REFERENCES b（id））

从逻辑上讲，它与示例 1相同。在没有查看MySQL源代码的情况下，我强烈怀疑外键是根据其名称按词法顺序“应用”的。这种情况下的标准行为 (ANSI-SQL) 是什么？

阅读这篇文章后，我可以想象行将如何作为主键顺序（例如自动增量键）在单个数据库页面中排列。但我想知道在复合主键的情况下它会如何。例如考虑一个 pk(a integer, b integer)，在数据库页面中，行是按照 a 列还是 b 列排列的？

我在 mysql 5.7 中试验 text 和 varchars。我创建一个简单的mysql表如下

CREATE TABLE small_table(
   pkint(11) NOT NULL AUTO_INCREMENT,
   varcvarchar(255) DEFAULT NULL,
   txttext,
  主键 ( pk)
) 引擎=InnoDB AUTO_INCREMENT=103925 默认字符集=utf8

该表有大约 100k 行。

我已经执行了以下查询并分析了两者

查询 1（按 varchar 列分组）

select varc,count(*) from small_table group by varc;

配置文件 1 结果

+----------+----------+
| 状态 | 持续时间 |
+----------+----------+
| 出发| 0.000064 |
| 检查权限 | 0.000004 |
| 开桌 | 0.004812 |
| 初始化 | 0.000026 |
| 系统锁 | 0.000005 |
| 优化 | 0.000006 |
| 统计 | 0.000010 |
| 准备| 0.000006 |
| 创建 tmp 表 | 0.000021 |
| 排序结果 | 0.000003 |
| 执行 | 0.000001 |
| 发送数据 | 0.052795 |
| 创建排序索引 | 0.000049 |
| 结束 | 0.000003 |
| 查询结束 | 0.000006 |
| 删除 tmp 表 | 0.000004 |
| 查询结束 | 0.000002 |
| 关闭表| 0.000006 |
| 释放物品 | 0.000021 |
| 清理| 0.000018 |
+----------+----------+

临时表详情1

+--------------+------+
| 变量名 | 价值 |
+--------------+------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 1 |
+--------------+------+

查询 2（按文本列分组）

select txt,count(*) from small_table group by txt；

配置文件 2 结果

+----------+------------+
| 状态 | 持续时间 |
+----------+------------+
| 出发| 0.000134 |
| 检查权限 | 0.000010 |
| 开桌 | 0.006690 |
| 初始化 | 0.000034 |
| 系统锁 | 0.000007 |
| 优化 | 0.000004 |
| 统计 | 0.000017 |
| 准备| 0.000008 |
| 创建 tmp 表 | 0.000027 |
| 排序结果 | 0.000005 |
| 执行 | 0.000002 |
| 发送数据 | 26.359877 |
| 创建排序索引 | 0.001042 |
| 结束 | 0.000007 |
| 查询结束 | 0.000007 |
| 删除 tmp 表 | 0.000208 |
| 查询结束 | 0.000003 |
| 关闭表| 0.000007 |
| 释放物品 | 0.000028 |
| 清理| 0.000020 |
+----------+------------+

临时表详情2

+--------------+------+
| 变量名 | 价值 |
+--------------+------+
| Created_tmp_disk_tables | 1 |
| Created_tmp_tables | 1 |
+--------------+------+

两个配置文件在每个阶段都花费了几乎相同的时间，除了“发送数据阶段”。第一个耗时 0.052795 秒，第二个耗时 26.359877 秒。为什么那个阶段差别这么大？（没有 group by 子句的相同查询在“发送数据阶段”中没有区别）

我有一张 100k 行的表

创建表`small_table`（
  `pk` int(11) NOT NULL AUTO_INCREMENT，
  `varc` varchar(255) 默认为空，
  `txt` 文本，
  主键 (`pk`)
) ENGINE=InnoDB AUTO_INCREMENT=103925 默认字符集=utf8

我执行了以下查询

select varc,count(*) from small_table group by varc;

和我一样

+--------------------+----------+
| 状态 | 持续时间 |
+--------------------+----------+
| 开始| 0.000060 |
| 检查权限 | 0.000010 |
| 打开表格 | 0.004685 |
| 初始化 | 0.000025 |
| 系统锁 | 0.000006 |
| 优化 | 0.000002 |
| 统计 | 0.000010 |
| 准备 | 0.000006 |
| 创建 tmp 表 | 0.000020 |
| 排序结果 | 0.000003 |
| 执行 | 0.000005 |
| 发送数据 | 0.001720 |
| 创建排序索引 | 0.000033 |
| 结束 | 0.000002 |
| 查询结束 | 0.000004 |
| 删除 tmp 表 | 0.000004 |
| 查询结束 | 0.000002 |
| 关闭表 | 0.000004 |
| 释放物品 | 0.000015 |
| 清理 | 0.000007 |
+--------------------+----------+

该配置文件具有正在创建 tmp 表状态（根据 mysql 文档，这意味着 tmp 表是在内存或磁盘中创建的）。我怀疑配置文件没有Copying to tmp table状态（在状态期间，服务器将复制到内存中的临时表。）我的假设是它已经创建了一个 tmp 表并且没有使用它。我对吗？

我的复制到 tmp 表状态在哪里？

情况如下：

1. 我遇到了几篇关于“ utf8_bin vs utf8_general_ci ”的帖子。
2. 同样，“ utf8_general_cs vs utf8_general_ci ”。

但我找不到“ utf8_bin vs utf8_general_cs ”的帖子。那么，它们是一样的吗？如果不是，它们之间有什么区别？

注意：我检查了默认 MySQL 中没有utf8_general_cs 。我可以知道为什么吗？

我需要一些关于Postgres中行级锁定的见解。

1. 在关系中的特定元组上可以并发共享锁（FOR SHARE）的数量。
2. 将在同一个元组上有许多共享锁会导致任何性能/或数据库问题。
3. 如果存在此类限制，如何配置它们以及建议的限制。

我有一张名为“汽车”的桌子

              表“db145004db.car”
  栏目 | 类型 | 整理 | 可空 | 默认
----------+--------+------------+--------+------ --
 车号 | 二进制 | | 不为空 |
 车名 | 文字 | | |
指标：
    "car_pkey" 主键，btree (car_no)

表格内容：

车号 | 车名  
------+------------
      1 | 第一辆车
      2 | 第二辆车
      3 | 第三辆车

我在两笔交易之后运行了这个，

事务 1：

开始 ;
select * from car where car_no=2 进行更新；

交易 2：

开始 ;
select * from car where car_no=2 进行更新；（等待第一个事务释放锁）

我的“pg_locks”表如下所示：

  锁型 | 数据库 | 关系 | 页| 元组 | virtualxid | 事务编号 | 分类 | 对象 | 对象 | 虚拟交易 | 进程号 | 模式 | 授予 | 快速通道
--------------+----------+----------+------+----- --+------------+----------------+--------+--------+ ----------+--------------------+--------+---------- ----------+--------+--------
 关系 | 103777 | 143022 | | | | | | | | 4/75 | 11043 | 访问共享锁 | 吨 | 吨
 关系 | 103777 | 143016 | | | | | | | | 4/75 | 11043 | 行共享锁 | 吨 | 吨
 virtualxid | | | | | 4/75 | | | | | 4/75 | 11043 | 独占锁 | 吨 | 吨
 关系 | 103777 | 143022 | | | | | | | | 5/144 | 11150 | 访问共享锁 | 吨 | 吨
 关系 | 103777 | 143016 | | | | | | | | 5/144 | 11150 | 行共享锁 | 吨 | 吨
 virtualxid | | | | | 5/144 | | | | | 5/144 | 11150 | 独占锁 | 吨 | 吨
 关系 | 103777 | 11577 | | | | | | | | 3/141 | 11263 | 访问共享锁 | 吨 | 吨
 virtualxid | | | | | 3/141 | | | | | 3/141 | 11263 | 独占锁 | 吨 | 吨
 事务编号 | | | | | | 10204 | | | | 5/144 | 11150 | 共享锁 | f | F
 元组 | 103777 | 143016 | 0 | 2 | | | | | | 5/144 | 11150 | 访问独占锁 | 吨 | F
 事务编号 | | | | | | 10204 | | | | 4/75 | 11043 | 独占锁 | 吨 | F

我看不到任何与行/元组相关的锁 (car_no=2)。此外，locktype 和 mode 有点令人困惑（我不明白他们倾向于传达什么）。作为 psql 的新手，我完全感到困惑。关于上述情况，任何人都可以帮助解释关于行级锁定的“pg_locks”。

我最近在 Postgres 中遇到了这种奇怪的行为。我有一张如下表：

sasdb=# \d emp_manager_rel
           表“db3004db.emp_manager_rel”
   专栏 | 类型 | 整理 | 可空 | 默认
------------+--------+------------+----------+----- ----
 emp_id | 大整数 | | |
 manager_id | 大整数 | | |

从 emp_manager_rel 中选择 * ；
 emp_id | manager_id
--------+------------
      1 | 123

我执行了以下更新语句：

更新 1：

更新 emp_manager_rel 设置 manager_id = manager_id+emp_id , emp_id=emp_id*4;

更新表格如下：

emp_id | manager_id
--------+------------
      4 | 124

更新 2：我执行了以下查询（在原始表上，而不是在更新后）

更新 emp_manager_rel 设置 emp_id=emp_id*4 ， manager_id = manager_id+emp_id ；

它更新表格如下：

emp_id | manager_id
--------+------------
      4 | 124

我预计 UPDATE 2 上 manager_id 的值为 127（因为 emp_id 已被 empid * 4 更改为 4）。但是，两个 UPDATES 产生相同的结果。我想知道 ANSI 标准中 set 子句的执行顺序是什么。

在 MySQL 5.7 中

1. alter table mydb.roles add constraint uk1 unique (role_name);

2. alter table mydb.roles add constraint uk2 unique (role_name);

第二条^命令 alter向我发出警告：

在表“world_x.roles”上定义了重复索引“uk2”。这已被弃用，在未来的版本中将被禁止。

当我在 Postgres(10.x) 中尝试同样的操作时，它没有警告我。

那么，在这种情况下，SQL 标准是什么？