主页 / user-139473

jonggu's questions

Martin Hope
jonggu
Asked: 2018-11-28 22:28:43 +0800 CST
  • 1

服务应用是nodejs，它使用的是mysql连接池。

目前，我们将计划向另一个国家出售服务 设置时区的最佳方式是什么？

时区设置执行，查询执行？像“设置时区=‘UTC’；从用户中选择*；”

mysql-5.6 timezone
Martin Hope
jonggu
Asked: 2018-07-21 00:16:12 +0800 CST
  • 1

我正在设计表关系。我有一个问题。

父母：一张桌子

孩子：B表

Table: A
PK: id

---

Table: B
PK: id
Column: A_id(NOT NULL), desk, chair...
Option: CONSTRAINT `fk_B_A_id` FOREIGN KEY (`A_id`) REFERENCES `A` (`id`) 
        ON DELETE NO ACTION ON UPDATE CASCADE

我的情况是，B表有时有A_id，有时没有A_id。

所以我试图在表不需要该0值A_id时插入。但是表没有 id 。结果发生错误。BA_idA0

我在想两种解决方案：

  1. A_id可以有null。
  2. A表创建时，虚拟数据（A.id=0）插入到A表中。

如果您有其他想法，请告诉我。

谢谢你。

database-design mysql-5.6
Martin Hope
jonggu
Asked: 2018-03-28 01:51:44 +0800 CST
  • 2

我想知道一个 sql 命令。

我正在使用 MariaDB 10.0.33

表架构就像

+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| site_cd         | varchar(20)  | NO   | PRI | NULL    |       |
| site_fair_cd    | varchar(20)  | NO   | PRI | NULL    |       |
| detail_cnt      | varchar(10)  | NO   | PRI | NULL    |       |
| name            | varchar(50)  | YES  |     | NULL    |       |
| little_title    | varchar(50)  | YES  |     | NULL    |       |
| info_text       | varchar(255) | YES  |     | NULL    |       |
| yoyaku          | varchar(20)  | YES  |     | NULL    |       |
| charge          | varchar(20)  | YES  |     | NULL    |       |
| person          | varchar(10)  | YES  |     | NULL    |       |
| price           | varchar(50)  | YES  |     | NULL    |       |
| remainder       | varchar(20)  | YES  |     | NULL    |       |
| date            | date         | YES  |     | NULL    |       |
| time            | varchar(50)  | YES  |     | NULL    |       |
| start_sec       | int(11)      | YES  |     | NULL    |       |
| end_sec         | int(11)      | YES  |     | NULL    |       |
| url             | varchar(200) | YES  |     | NULL    |       |
| crwl_date       | datetime     | YES  | MUL | NULL    |       |
| regist          | datetime     | YES  |     | NULL    |       |
+-----------------+--------------+------+-----+---------+-------+

索引就像

+----------------------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table                      | Non_unique | Key_name        | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| table_name                 |          1 | crwl_date_index |            1 | crwl_date       | A         |       22237 |     NULL | NULL   | YES  | BTREE      |         |               |

SQL 是两种模式。

首先是如下。

EXPLAIN SELECT  SQL_NO_CACHE DATE(crwl_date) AS date,count(*) AS cnt
    FROM  table_name
    WHERE  crwl_date >= '2018-03-20'
      AND  crwl_date <  '2018-03-28'
    GROUP BY  DATE(crwl_date);
+------+-------------+----------------------------+-------+-----------------+-----------------+---------+------+-------+-----------------------------------------------------------+
| id   | select_type | table                      | type  | possible_keys   | key             | key_len | ref  | rows  | Extra                                                     |
+------+-------------+----------------------------+-------+-----------------+-----------------+---------+------+-------+-----------------------------------------------------------+
|    1 | SIMPLE      | table_name                 | range | crwl_date_index | crwl_date_index | 9       | NULL | 43958 | Using where; Using index; Using temporary; Using filesort |
+------+-------------+----------------------------+-------+-----------------+-----------------+---------+------+-------+-----------------------------------------------------------+

二是如下。

EXPLAIN SELECT  SQL_NO_CACHE DATE(crwl_date) AS date,count(*) AS cnt
    FROM  table_name
    WHERE  crwl_date >= '2018-03-20'
      AND  crwl_date <  '2018-03-28'
    GROUP BY  DATE(crwl_date)
    ORDER BY  NULL;
+------+-------------+----------------------------+-------+-----------------+-----------------+---------+------+-------+-------------------------------------------+
| id   | select_type | table                      | type  | possible_keys   | key             | key_len | ref  | rows  | Extra                                     |
+------+-------------+----------------------------+-------+-----------------+-----------------+---------+------+-------+-------------------------------------------+
|    1 | SIMPLE      | table_name                 | range | crwl_date_index | crwl_date_index | 9       | NULL | 43958 | Using where; Using index; Using temporary |
+------+-------------+----------------------------+-------+-----------------+-----------------+---------+------+-------+-------------------------------------------+

第二个 SQL 包括“ORDER BY NULL”。所以“使用文件排序”消失了。

我不明白为什么会这样。请解释。

mariadb