mysql也要支持递归with

〇〇

〇〇 发表于 2017-6-13 13:04
union 会去重复
MariaDB [test]> WITH RECURSIVE t as(select 1 a,1 b union select a+0,b*1 from t w
...

递归部分不得包含分析函数和聚合函数，oracle可以包含分析函数
MariaDB [test]> WITH RECURSIVE t as(select 1 a,1 b union select max(a),min(b) fr
om t where a<10)select * from t;
ERROR 4008 (HY000): Restrictions imposed on recursive definitions are violated f
or table 't'
MariaDB [test]> WITH RECURSIVE t as(select 1 a,1 b union select max(a)over(),min
(b)over() from t where a<10)select * from t;
ERROR 4008 (HY000): Restrictions imposed on recursive definitions are violated f
or table 't'
MariaDB [test]> WITH RECURSIVE t as(select 1 a,1 b) select max(a)over(),min(b)ov
er() from t where a<10;
+--------------+--------------+
| max(a)over() | min(b)over() |
+--------------+--------------+
|            1 |            1 |
+--------------+--------------+
1 row in set (0.00 sec)

〇〇

MariaDB分析函数
MariaDB [test]> WITH RECURSIVE t as(select 1 a,1 b union all select a+1,b*2 from
t where a<10)select a,sum(a)over() from t;
+----+--------------+
| a  | sum(a)over() |
+----+--------------+
|  1 | 55           |
|  2 | 55           |
|  3 | 55           |
|  4 | 55           |
|  5 | 55           |
|  6 | 55           |
|  7 | 55           |
|  8 | 55           |
|  9 | 55           |
| 10 | 55           |
+----+--------------+
10 rows in set (0.02 sec)

MariaDB [test]> WITH RECURSIVE t as(select 1 a,1 b union all select a+1,b*2 from
t where a<10)select a,sum(a)over(order by a) from t;
+----+------------------------+
| a  | sum(a)over(order by a) |
+----+------------------------+
|  1 | 1                      |
|  2 | 3                      |
|  3 | 6                      |
|  4 | 10                     |
|  5 | 15                     |
|  6 | 21                     |
|  7 | 28                     |
|  8 | 36                     |
|  9 | 45                     |
| 10 | 55                     |
+----+------------------------+
10 rows in set (0.02 sec)

〇〇

〇〇 发表于 2017-6-13 13:18
MariaDB分析函数
MariaDB [test]> WITH RECURSIVE t as(select 1 a,1 b union all select a+1,b*2 from
  ...

分区分析函数
MariaDB [test]> WITH RECURSIVE t as(select 1 a,1 b union all select a+1,b*2 from
t where a<10)select a,sum(a)over(partition by mod(a,2) order by a),mod(a,2) fro
m t;
+----+----------------------------------------------+----------+
| a  | sum(a)over(partition by mod(a,2) order by a) | mod(a,2) |
+----+----------------------------------------------+----------+
|  1 | 1                                            |        1 |
|  2 | 2                                            |        0 |
|  3 | 4                                            |        1 |
|  4 | 6                                            |        0 |
|  5 | 9                                            |        1 |
|  6 | 12                                           |        0 |
|  7 | 16                                           |        1 |
|  8 | 20                                           |        0 |
|  9 | 25                                           |        1 |
| 10 | 30                                           |        0 |
+----+----------------------------------------------+----------+
10 rows in set (0.00 sec)

〇〇

〇〇 发表于 2017-6-13 13:04
union 会去重复
MariaDB [test]> WITH RECURSIVE t as(select 1 a,1 b union select a+0,b*1 from t w
...

居然a<10的条件没有无限循环

〇〇

性能还是比不上oracle
profile对各个语句计时，包括执行出错的
MariaDB [test]> set profiling=1;
Query OK, 0 rows affected (0.03 sec)

MariaDB [test]> WITH RECURSIVE t as(select 1 a,1 b union all select a+1,b*2 from
t where a<10)select a,sum(a)over(partition by mod(a,2) order by a),mod(a,2) fro
m t;
+----+----------------------------------------------+----------+
| a  | sum(a)over(partition by mod(a,2) order by a) | mod(a,2) |
+----+----------------------------------------------+----------+
|  1 | 1                                            |        1 |
|  2 | 2                                            |        0 |
|  3 | 4                                            |        1 |
|  4 | 6                                            |        0 |
|  5 | 9                                            |        1 |
|  6 | 12                                           |        0 |
|  7 | 16                                           |        1 |
|  8 | 20                                           |        0 |
|  9 | 25                                           |        1 |
| 10 | 30                                           |        0 |
+----+----------------------------------------------+----------+
10 rows in set (0.00 sec)

MariaDB [test]> show profiles;
+----------+------------+-------------------------------------------------------
--------------------------------------------------------------------------------
--------------+
| Query_ID | Duration   | Query

              |
+----------+------------+-------------------------------------------------------
--------------------------------------------------------------------------------
--------------+
|        1 | 0.00071210 | WITH RECURSIVE t as(select 1 a,1 b union all select a+
1,b*2 from t where a<10)select a,sum(a)over(partition by mod(a,2) order by a),mo
d(a,2) from t |
+----------+------------+-------------------------------------------------------
--------------------------------------------------------------------------------
--------------+
1 row in set (0.01 sec)

MariaDB [test]> WITH RECURSIVE t as(select 1 a,1 b union all select a+1,b*2 from
t where a<1e6)select sum(a) from t;
ERROR 1690 (22003): BIGINT value is out of range in '`t`.`b` * 2'
MariaDB [test]> WITH RECURSIVE t as(select 1 a,1 b union all select a+1,b from t
where a<1e6)select sum(a) from t;
+--------------+
| sum(a)       |
+--------------+
| 500000500000 |
+--------------+
1 row in set (7.58 sec)

MariaDB [test]> show profiles;
+----------+------------+-------------------------------------------------------
--------------------------------------------------------------------------------
--------------+
| Query_ID | Duration   | Query

              |
+----------+------------+-------------------------------------------------------
--------------------------------------------------------------------------------
--------------+
|        1 | 0.00071210 | WITH RECURSIVE t as(select 1 a,1 b union all select a+
1,b*2 from t where a<10)select a,sum(a)over(partition by mod(a,2) order by a),mo
d(a,2) from t |
|        2 | 0.00250339 | WITH RECURSIVE t as(select 1 a,1 b union all select a+
1,b*2 from t where a<1e6)select sum(a) from t
              |
|        3 | 7.58109967 | WITH RECURSIVE t as(select 1 a,1 b union all select a+
1,b from t where a<1e6)select sum(a) from t
              |
+----------+------------+-------------------------------------------------------
--------------------------------------------------------------------------------
--------------+
3 rows in set (0.00 sec)

MariaDB [test]>

〇〇

〇〇 发表于 2017-6-13 13:44
性能还是比不上oracle
profile对各个语句计时，包括执行出错的
MariaDB [test]> set profiling=1;

我错了，刚才的慢是打开了profiler的原因
MariaDB [test]>  set profiling=0;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> WITH RECURSIVE t as(select 1 a,1 b union all select a+1,b from t
where a<1e6)select sum(a) from t;
+--------------+
| sum(a)       |
+--------------+
| 500000500000 |
+--------------+
1 row in set (3.82 sec)

MariaDB [test]> WITH RECURSIVE t as(select 1 a,1 b union all select a+1,b from t
where a<2e6)select sum(a) from t;
+---------------+
| sum(a)        |
+---------------+
| 2000001000000 |
+---------------+
1 row in set (12.14 sec)

MariaDB [test]> WITH RECURSIVE t as(select 1 a,1 b union all select a+1,b from t
where a<1.5e6)select sum(a) from t;
+---------------+
| sum(a)        |
+---------------+
| 1125000750000 |
+---------------+
1 row in set (7.89 sec)

MariaDB [test]>

〇〇

〇〇 发表于 2017-6-13 13:48
我错了，刚才的慢是打开了profiler的原因
MariaDB [test]>  set profiling=0;
Query OK, 0 rows affect ...

profile统计的信息
MariaDB [test]> show profile for query 3;

| Sending data            | 0.000006 |
| executing               | 0.000002 |
| Sending data            | 1.391830 |
| converting HEAP to Aria | 0.061510 |
| Sending data            | 0.814728 |
| end                     | 0.002081 |
| removing tmp table      | 0.002589 |
| end                     | 0.000010 |
| removing tmp table      | 0.000005 |
| end                     | 0.000005 |
| query end               | 0.000004 |
| closing tables          | 0.000002 |
| removing tmp table      | 0.003242 |
| closing tables          | 0.000018 |
| removing tmp table      | 0.000009 |
| closing tables          | 0.000005 |
| Unlocking tables        | 0.000008 |
| freeing items           | 0.000008 |
| updating status         | 0.000138 |
| cleaning up             | 0.000004 |
+-------------------------+----------+
100 rows in set (0.00 sec)

〇〇

汉字字符串字面量和列居然长度不同
MariaDB [test]> select length('敲') ;
+--------------+
| length('敲') |
+--------------+
|            2 |
+--------------+
1 row in set (0.00 sec)

MariaDB [test]> select char_length('敲') ;
+-------------------+
| char_length('敲') |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

MariaDB [test]> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | gbk                        |
| character_set_connection | gbk                        |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | gbk                        |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | D:\MariaDB\share\charsets\ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

MariaDB [test]> create table t1 (a varchar(100));
Query OK, 0 rows affected (0.55 sec)

MariaDB [test]> insert into t1 values('敲');
Query OK, 1 row affected (0.10 sec)

MariaDB [test]> select * from t1;
+------+
| a    |
+------+
| 敲   |
+------+
1 row in set (0.00 sec)

MariaDB [test]> select length(a) from t1;
+-----------+
| length(a) |
+-----------+
|         3 |
+-----------+
1 row in set (0.00 sec)--下面这个最奇怪
MariaDB [test]> select length(a) from t1 where a='敲';
+-----------+
| length(a) |
+-----------+
|         3 |
+-----------+
1 row in set (0.02 sec)

xxc_weil

Naldonado 发表于 2017-6-2 11:00
艹，居然还是递归with！也就是说oralce，sqlserver，屁股，mysql四大库都支持递归with了。

大神们咋都叫屁股呢，啥来由哦

〇〇

〇〇 发表于 2017-6-13 14:18
汉字字符串字面量和列居然长度不同
MariaDB [test]> select length('敲') ;
+--------------+

oracle在utf8下没有=2的情况
SQL> select length('敲')from dual;

LENGTH('敲')
------------
           1

SQL> select lengthb('敲')from dual;

LENGTHB('敲')
-------------
            3


SQL> col parameter for a40
SQL>  select * from nls_session_parameters;

PARAMETER                                VALUE
---------------------------------------- -----------------------------
NLS_LANGUAGE                             SIMPLIFIED CHINESE
NLS_TERRITORY                            CHINA
NLS_CURRENCY                             ￥
NLS_ISO_CURRENCY                         CHINA
NLS_NUMERIC_CHARACTERS                   .,
NLS_CALENDAR                             GREGORIAN
NLS_DATE_FORMAT                          DD-MON-RR
NLS_DATE_LANGUAGE                        SIMPLIFIED CHINESE
NLS_SORT                                 BINARY
NLS_TIME_FORMAT                          HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT                     DD-MON-RR HH.MI.SSXFF AM

PARAMETER                                VALUE
---------------------------------------- -----------------------------
NLS_TIME_TZ_FORMAT                       HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT                  DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY                        ￥
NLS_COMP                                 BINARY
NLS_LENGTH_SEMANTICS                     BYTE
NLS_NCHAR_CONV_EXCP                      FALSE

已选择17行。

SQL> select * from nls_database_parameters
  2  /

PARAMETER                                VALUE
---------------------------------------- -----------------------------
NLS_LANGUAGE                             AMERICAN
NLS_TERRITORY                            AMERICA
NLS_CURRENCY                             $
NLS_ISO_CURRENCY                         AMERICA
NLS_NUMERIC_CHARACTERS                   .,
NLS_CHARACTERSET                         AL32UTF8
NLS_CALENDAR                             GREGORIAN
NLS_DATE_FORMAT                          DD-MON-RR
NLS_DATE_LANGUAGE                        AMERICAN
NLS_SORT                                 BINARY
NLS_TIME_FORMAT                          HH.MI.SSXFF AM

PARAMETER                                VALUE
---------------------------------------- -----------------------------
NLS_TIMESTAMP_FORMAT                     DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT                       HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT                  DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY                        $
NLS_COMP                                 BINARY
NLS_LENGTH_SEMANTICS                     BYTE
NLS_NCHAR_CONV_EXCP                      FALSE
NLS_NCHAR_CHARACTERSET                   AL16UTF16
NLS_RDBMS_VERSION                        11.2.0.2.0

已选择20行。