POSTED BY
2023-01-20

すぐに忘れてしまうのでメモ。

まずはテーブル定義を確認する。

show create table article;

| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             | article | CREATE TABLE `article` (
  `id` varchar(128) NOT NULL,
  `title` text NOT NULL,
  `plain` text NOT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `title` (`title`),
  FULLTEXT KEY `plain` (`plain`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |

インデックスがいくつかあるようだ。詳細を確認する。

show index from article;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| article |          0 | PRIMARY  |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| article |          1 | title    |            1 | title       | NULL      |        NULL |     NULL | NULL   |      | FULLTEXT   |         |               | NO      |
| article |          1 | plain    |            1 | plain       | NULL      |        NULL |     NULL | NULL   |      | FULLTEXT   |         |               | NO      |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+

上記フルテキストインデックスでの検索だが、ちゃんと含む文字列を指定しているのに出なかったり、いまいちなので、通常のインデックスに変更したい。よって、まずこの全文検索インデックスどもを削除する。

drop index title on article;
Query OK, 0 rows affected (0.011 sec)
Records: 0  Duplicates: 0  Warnings: 0

drop index plain on article;
Query OK, 0 rows affected (0.015 sec)
Records: 0  Duplicates: 0  Warnings: 0

削除されたか確認

show index from article;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| article |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               | NO      |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+

show create table article;
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                         | article | CREATE TABLE `article` (
  `no` float NOT NULL,
  `id` varchar(128) NOT NULL,
  `date` timestamp NOT NULL DEFAULT current_timestamp(),
  `upd_date` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `title` text NOT NULL,
  `honbun` text NOT NULL,
  `plain` text NOT NULL,
  `display` text NOT NULL,
  `category` varchar(128) NOT NULL,
  `status` smallint(6) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |

通常のインデックスに作り直す

create index title on article(title);
Query OK, 0 rows affected, 1 warning (0.004 sec)
Records: 0  Duplicates: 0  Warnings: 1

create index plain on article(plain);
Query OK, 0 rows affected, 1 warning (0.009 sec)
Records: 0  Duplicates: 0  Warnings: 1

作成されたか確認

show create table article;
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       | article | CREATE TABLE `article` (
  `no` float NOT NULL,
  `id` varchar(128) NOT NULL,
  `date` timestamp NOT NULL DEFAULT current_timestamp(),
  `upd_date` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `title` text NOT NULL,
  `honbun` text NOT NULL,
  `plain` text NOT NULL,
  `display` text NOT NULL,
  `category` varchar(128) NOT NULL,
  `status` smallint(6) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `title` (`title`(1024)),
  KEY `plain` (`plain`(1024))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |

show index from article;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| article |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| article |          1 | title    |            1 | title       | A         |           0 |     1024 | NULL   |      | BTREE      |         |               | NO      |
| article |          1 | plain    |            1 | plain       | A         |           0 |     1024 | NULL   |      | BTREE      |         |               | NO      |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+

んん…？？1024ってなんだ？？と調べたら、Sub_partといって、最初の1024バイトのみをインデックスするということらしい。これを256バイトとかにしたければ、

create index title on article(title(256));

などと指定するらしいが、全文検索インデックスではデフォルトはNULL＝文字数無制限＝であったと思うので、同じように1024でなく無制限にしたい。

なのでNULLを指定して作り直そうとしたら…

drop index plain on article;
Query OK, 0 rows affected (0.006 sec)
Records: 0  Duplicates: 0  Warnings: 0

create index plain on article(plain(NULL));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NULL))' at line 1

と、怒られてエラーになってしまった。NULLでなく256とかなら成功するので、通常インデックスは最大でも1024バイトまでなのか…？？MariaDBだからかもしれないが…。