5.2. SQLコマンド#

このセクションでは、利用可能なSQLコマンドと利用できないSQLコマンドを記載しています。

5.2.1. 利用可能なSQLコマンドの一覧#

  • ALTER TABLE table_name ADD COLUMN column_name TEXT

  • ALTER TABLE table_name ADD FULLTEXT INDEX index_name (column_name)

  • ALTER TABLE table_name ADD SPATIAL KEY index_name (geometry_column_name)

  • ALTER TABLE table_name ENGINE = mroonga

  • ALTER TABLE table_name RENAME new_table_name

  • COMMIT

  • CREATE FULLTEXT INDEX index_name ON table_name(column_name)

  • CREATE FULLTEXT INDEX index_name ON table_name(column_name, ...)

  • CREATE TABLE IF NOT EXISTS table_name (...)

  • CREATE TABLE \`table-name-with-hyphen\` (...)

  • CREATE TABLE table_name (... FULLTEXT INDEX index_name (column_name) COMMENT 'tokenizer "TokenMecab"')

  • CREATE TABLE table_name (... FULLTEXT INDEX index_name (column_name) COMMENT 'normalizer "NormalizerMySQLUnicodeCIExceptKanaCIKanaWithVoicedSoundMark"')

  • CREATE TABLE table_name (... FULLTEXT INDEX index_name (column_name) COMMENT 'tokenizer "TokenDelimit", normalizer "NormalizerMySQLUnicodeCIExceptKanaCIKanaWithVoicedSoundMark"')

  • CREATE TABLE table_name (... FULLTEXT INDEX index_name (column_name))

  • CREATE TABLE table_name (... FULLTEXT INDEX index_name (column_name, ...))

  • CREATE TABLE table_name (... INDEX (column_name))

  • CREATE TABLE table_name (... INDEX (column_name, ...))

  • CREATE TABLE table_name (... INDEX USING BTREE (column_name))

  • CREATE TABLE table_name (... PRIMARY KEY (column_name))

  • CREATE TABLE table_name (...) CHARSET ASCII

  • CREATE TABLE table_name (...) CHARSET BINARY

  • CREATE TABLE table_name (...) CHARSET CP932

  • CREATE TABLE table_name (...) CHARSET EUCJPMS

  • CREATE TABLE table_name (...) CHARSET KOI8R

  • CREATE TABLE table_name (...) CHARSET LATIN1

  • CREATE TABLE table_name (...) CHARSET SJIS

  • CREATE TABLE table_name (...) CHARSET UJIS

  • CREATE TABLE table_name (...) CHARSET UTF8

  • CREATE TABLE table_name (...) CHARSET UTF8MB4

  • CREATE TABLE table_name (...) ENGINE=mroonga COMMENT = 'ENGINE "InnoDB"'

  • CREATE TABLE table_name (...) ENGINE=mroonga

  • CREATE TABLE table_name (\`_id\` INT)

  • CREATE TABLE table_name (\`_id\` INT, KEY(_id) USING HASH)

  • CREATE TABLE table_name (column_name BIGINT UNSIGNED)

  • CREATE TABLE table_name (column_name BIGINT)

  • CREATE TABLE table_name (column_name BINARY(...))

  • CREATE TABLE table_name (column_name BIT)

  • CREATE TABLE table_name (column_name BLOB)

  • CREATE TABLE table_name (column_name CHAR(...))

  • CREATE TABLE table_name (column_name DATE)

  • CREATE TABLE table_name (column_name DATETIME)

  • CREATE TABLE table_name (column_name DECIMAL)

  • CREATE TABLE table_name (column_name DOUBLE)

  • CREATE TABLE table_name (column_name ENUM(...))

  • CREATE TABLE table_name (column_name FLOAT)

  • CREATE TABLE table_name (column_name GEOMETRY NOT NULL)

  • CREATE TABLE table_name (column_name INT UNSIGNED)

  • CREATE TABLE table_name (column_name INT)

  • CREATE TABLE table_name (column_name LONGBLOB)

  • CREATE TABLE table_name (column_name LONGTEXT)

  • CREATE TABLE table_name (column_name MEDIUMBLOB)

  • CREATE TABLE table_name (column_name MEDIUMINT UNSIGNED)

  • CREATE TABLE table_name (column_name MEDIUMINT)

  • CREATE TABLE table_name (column_name MEDIUMTEXT)

  • CREATE TABLE table_name (column_name SET(...))

  • CREATE TABLE table_name (column_name SMALLINT UNSIGNED)

  • CREATE TABLE table_name (column_name SMALLINT)

  • CREATE TABLE table_name (column_name TEXT)

  • CREATE TABLE table_name (column_name TIME)

  • CREATE TABLE table_name (column_name TIMESTAMP)

  • CREATE TABLE table_name (column_name TINYBLOB)

  • CREATE TABLE table_name (column_name TINYINT UNSIGNED)

  • CREATE TABLE table_name (column_name TINYINT)

  • CREATE TABLE table_name (column_name TINYTEXT)

  • CREATE TABLE table_name (column_name VARBINARY(...))

  • CREATE TABLE table_name (column_name VARCHAR(...))

  • CREATE TABLE table_name (column_name YEAR)

  • CREATE TABLE table_name (column_name INT AUTO_INCREMENT)

  • CREATE TABLE table_name (column_name INT PRIMARY KEY)

  • CREATE TABLE table_name (column_name DATE PRIMARY KEY)

  • CREATE TABLE table_name (column_name DATETIME PRIMARY KEY)

  • CREATE TABLE table_name (column_name DATETIME(fractional_seconds_precision) PRIMARY KEY)

  • CREATE TABLE table_name (column_name DECIMAL PRIMARY KEY)

  • CREATE TABLE table_name (column_name DECIMAL(maximum_number_digits, fractional_seconds_precision) PRIMARY KEY)

  • CREATE TABLE table_name (column_name TIME PRIMARY KEY)

  • CREATE TABLE table_name (column_name TIME(fractional_seconds_precision) PRIMARY KEY)

  • CREATE TABLE table_name (column_name TIMESTAMP PRIMARY KEY)

  • CREATE TABLE table_name (column_name TIMESTAMP(fractional_seconds_precision) PRIMARY KEY)

  • CREATE TABLE table_name (column_name YEAR PRIMARY KEY)

  • CREATE TABLE table_name (column_name1 INT, column_name2 ..., KEY (column_name1, column_name2))

  • CREATE TABLE table_name (column_name1 DOUBLE, column_name2 ..., KEY (column_name1, column_name2))

  • CREATE TABLE table_name (column_name1 FLOAT, column_name2 ..., KEY (column_name1, column_name2))

  • CREATE TABLE table_name (column_name1 CHAR(...), column_name2 ..., KEY (column_name1, column_name2))

  • CREATE TABLE table_name (column_name1 VARCHAR(...), column_name2 ..., KEY (column_name1, column_name2))

  • CREATE TEMPORARY TABLE table_name (...)

  • DELETE FROM table_name WHERE ...

  • DROP INDEX column_name ON table_name

  • DROP TABLE IF EXISTS table_name, ...

  • DROP TABLE \`table-name-with-hyphen\`, ...

  • DROP TABLE table_name, ...

  • DROP TEMPORARY TABLE table_name

  • FLUSH LOGS

  • FLUSH TABLES

  • INSERT INTO (geometry_column_name) VALUES (GeomFromText('POINT(...)'))

  • INSERT INTO ... VALUES ...

  • INSERT INTO ... VALUES ... ON DUPLICATE KEY UPDATE ... (for PRIMARY KEY)

  • INSERT INTO ... VALUES ... ON DUPLICATE KEY UPDATE ... (for UNIQUE KEY)

  • INSERT INTO table_name (column_name, ...) SELECT ... FROM other_table_name

  • REPLACE INTO table_name SELECT ... FROM other_table_name ...

  • SELECT * FROM information_schema.plugins WHERE plugin_name = "mroonga"

  • SELECT * FROM table_name

  • SELECT * FROM table_name FORCE INDEX(index_name) WHERE ...

  • SELECT * FROM table_name ORDER BY column_name ASC LIMIT ...

  • SELECT * FROM table_name ORDER BY column_name ASC

  • SELECT * FROM table_name ORDER BY column_name DESC LIMIT ...

  • SELECT * FROM table_name ORDER BY column_name DESC

  • SELECT * FROM table_name ORDER BY column_name

  • SELECT * FROM table_name WHERE MATCH(column_name) AGAINST("*D+ word ..." IN BOOLEAN MODE)

  • SELECT * FROM table_name WHERE MATCH(column_name) AGAINST("*D- word ..." IN BOOLEAN MODE)

  • SELECT * FROM table_name WHERE MATCH(column_name) AGAINST("*DOR word ..." IN BOOLEAN MODE)

  • SELECT * FROM table_name WHERE MATCH(column_name) AGAINST("*W word ..." IN BOOLEAN MODE)

  • SELECT * FROM table_name WHERE MATCH(column_name) AGAINST("+word ..." IN BOOLEAN MODE)

  • SELECT * FROM table_name WHERE MATCH(column_name) AGAINST("..." IN BOOLEAN MODE)

  • SELECT * FROM table_name WHERE MATCH(column_name, ...) AGAINST("..." IN BOOLEAN MODE)

  • SELECT * FROM table_name WHERE MBRContains(GeomFromText('LineString(...)'), geometry_column_name)

  • SELECT * FROM table_name WHERE NOT MATCH(column_name) AGAINST("..." IN BOOLEAN MODE)

  • SELECT * FROM table_name WHERE column_name < ...

  • SELECT * FROM table_name WHERE column_name <= ...

  • SELECT * FROM table_name WHERE column_name = ...

  • SELECT * FROM table_name WHERE column_name > ...

  • SELECT * FROM table_name WHERE column_name >= ...

  • SELECT * FROM table_name WHERE column_name BETWEEN ... AND ...

  • SELECT * FROM table_name WHERE column_name IN (SELECT sub_column_name FROM sub_table_name WHERE MATCH(sub_text_column_name) AGAINST("..."))

  • SELECT *, MATCH(column_name) AGAINST("..." IN BOOLEAN MODE) FROM table_name WHERE MATCH(column_name) AGAINST("..." IN BOOLEAN MODE)

  • SELECT FOUND_ROWS()

  • SELECT SQL_CALC_FOUND_ROWS * FROM table_name WHERE MATCH(...) AGAINST("..." IN BOOLEAN MODE) ORDER BY column_name LIMIT start,n_records

  • SELECT last_insert_grn_id()

  • SET binlog_format="MIXED"

  • SET binlog_format="ROW"

  • SET binlog_format="STATEMENT"

  • TRUNCATE TABLE table_name

  • UPDATE table_name SET column_name = ...

5.2.2. 利用できないSQLコマンドの一覧#

  • CREATE TABLE (...) CHARSET 上記にないキャラクタセット

  • INSERT INTO (geometry_column_name) VALUES (GeomFromText('LineString(...)'))

  • INSERT INTO (...) VALUES (null)

  • START TRANSACTION

基本的には、Groongaがサポートしているキャラクタセット(EUC-JP/UTF-8/SJIS/LATIN1/KOI8R)はMroongaでもサポートされています。

5.2.3. リストにないSQLコマンドを見つけました#

もし、あなたがリストにないSQLコマンドを見つけて、そのSQLコマンドが動くかどうかを確認した場合は、そのSQLコマンドをこのリストに追加させてください。そのSQLコマンドを 開発チーム に連絡してもらうか、 ソースファイル に対するパッチをGitHubでpull requestしてくれれば取り込みます！