MariaDB [d1]> SELECT @@version; +--------------------+ | @@version | +--------------------+ | 10.1.0-MariaDB-log | +--------------------+ 1 row in set (0.00 sec) MariaDB [d1]> SHOW ENGINES; +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+ | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | TEST_SQL_DISCOVERY | YES | Minimal engine to test table discovery via sql statements | NO | NO | NO | | InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | FEDERATED | YES | FederatedX pluggable storage engine | YES | NO | YES | | CONNECT | YES | Management of External Data (SQL/MED), including many file formats | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | SPHINX | YES | Sphinx storage engine 2.1.5-release | NO | NO | NO | | SEQUENCE | YES | Generated tables filled with sequential values | YES | YES | YES | +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+ 14 rows in set (0.00 sec)
うぇーい(＾ω＾)
MariaDB [d1]> CREATE TABLE t1 (num int) Engine= SEQUENCE; ERROR 1005 (HY000): Can't create table `d1`.`t1` (errno: 131 "Command not supported by database")
あれ、なんか違うらしい。
確かに前(10.0に入るって時に)なんかちょっと見た気がするけど、Oracleのシーケンステーブル(?)とかとは根本的に違う何からしいんだよね確か。
おとなしくマニュアル読む。 https://mariadb.com/kb/en/mariadb/mariadb-documentation/mariadb-storage-engines/sequence/
使い方はこうらしい。
MariaDB [d1]> SELECT * FROM seq_1_to_12; +-----+ | seq | +-----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | | 10 | | 11 | | 12 | +-----+ 12 rows in set (0.00 sec) MariaDB [d1]> SELECT seq FROM seq_10_to_20_step_2; +-----+ | seq | +-----+ | 10 | | 12 | | 14 | | 16 | | 18 | | 20 | +-----+ 6 rows in set (0.00 sec)
お、おう。連番が欲しい時に便利そうだねってか、偶数だけ欲しい時にWHERE MOD(c1, 2) = 0とかしなくてもJOINすれば良いように…とかそんなかんじ？（思い付かない
MariaDB [d1]> SELECT seq FROM seq_1_to_99999999999999999999999999999999 ORDER BY seq DESC LIMIT 3; +----------------------+ | seq | +----------------------+ | 18446744073709551615 | | 18446744073709551614 | | 18446744073709551613 | +----------------------+ 3 rows in set (0.00 sec)
2 ^ 64 - 1までいけるっぽい。負値はダメ。
MariaDB [d1]> SELECT * FROM seq_10_to_1; +-----+ | seq | +-----+ | 10 | | 9 | | 8 | | 7 | | 6 | | 5 | | 4 | | 3 | | 2 | | 1 | +-----+ 10 rows in set (0.00 sec) MariaDB [d1]> SELECT * FROM seq_10_to_1_step_2; +-----+ | seq | +-----+ | 9 | | 7 | | 5 | | 3 | | 1 | +-----+ 5 rows in set (0.00 sec)
頭の方が数字が大きければ降順っぽく。
ただし、seq_1_to_10_step_2 は 「先の値」の1から2ずつインクリメントするのに対して、seq_10_to_1_step_2は「先の値」の10からデクリメントしてるわけではないので、たぶん単にseq_1_to_10_step_2をひっくり返してるだけ。
MariaDB [d1]> explain SELECT * FROM seq_1_to_10_step_2; +------+-------------+--------------------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+--------------------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | seq_1_to_10_step_2 | index | NULL | PRIMARY | 8 | NULL | 5 | Using index | +------+-------------+--------------------+-------+---------------+---------+---------+------+------+-------------+ 1 row in set (0.00 sec) MariaDB [d1]> explain SELECT * FROM seq_10_to_1_step_2; +------+-------------+--------------------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+--------------------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | seq_10_to_1_step_2 | ALL | NULL | NULL | NULL | NULL | 5 | | +------+-------------+--------------------+------+---------------+------+---------+------+------+-------+ 1 row in set (0.00 sec)
ちゃんとEXPLAIN取れるところは芸が細かいというかなんと言うか。
MariaDB [d1]> SELECT seq, CONCAT(IF(fizz.seq IS NULL, '', 'fizz'), IF(buzz.seq IS NULL, '', 'buzz')) AS str FROM seq_1_to_100 AS main LEFT JOIN seq_0_to_100_step_3 AS fizz USING(seq) LEFT JOIN seq_0_to_100_step_5 AS buzz USING(seq); +-----+----------+ | seq | str | +-----+----------+ | 1 | | | 2 | | | 3 | fizz | | 4 | | | 5 | buzz | | 6 | fizz | | 7 | | | 8 | | | 9 | fizz | | 10 | buzz | | 11 | | | 12 | fizz | | 13 | | | 14 | | | 15 | fizzbuzz | .. | 100 | buzz | +-----+----------+ 100 rows in set (0.00 sec)
FIZZBUZZしてみたけど、IF演算子使うならなんでもいいやね。。
0 件のコメント :
コメントを投稿