ALTER TABLE index ADD COLUMN column_name [{INTEGER|INT|BIGINT|FLOAT|BOOL|MULTI|MULTI64|JSON|STRING|TIMESTAMP|TEXT [INDEXED [ATTRIBUTE]]}] [engine='columnar']
ALTER TABLE index DROP COLUMN column_nameIt supports adding one field at a time for RT indexes. Supported data types are:
int- integer attributetimestamp- timestamp attributebigint- big integer attributefloat- float attributebool- boolean attributemulti- multi-valued integer attributemulti64- multi-valued bigint attributejson- json attributestring/text attribute/string attribute- string attributetext/text indexed stored/string indexed stored- full-text indexed field with original value stored in docstoretext indexed/string indexed- full-text indexed field, indexed only (the original value is not stored in docstore)text indexed attribute/string indexed attribute- full text indexed field + string attribute (not storing the original value in docstore)text stored/string stored- the value will be only stored in docstore, not full-text indexed, not a string attribute- adding
engine='columnar'to any attribute (except for json) will make it stored in the columnar storage
- Querying an index is impossible while a column is being added.
- Newly created attribute's values are set to 0.
ALTERwill not work for distributed indexes and indexes without any attributes.DROP COLUMNwill fail if an index has only one field.- When dropping a field which is both a full-text field and a string attribute the first
ALTER DROPdrops the attribute, the second one drops the full-text field. - Adding/dropping full-text field is only supported in RT mode.
- Example
Example
📋
mysql> desc rt;
+------------+-----------+
| Field | Type |
+------------+-----------+
| id | bigint |
| text | field |
| group_id | uint |
| date_added | timestamp |
+------------+-----------+
mysql> alter table rt add column test integer;
mysql> desc rt;
+------------+-----------+
| Field | Type |
+------------+-----------+
| id | bigint |
| text | field |
| group_id | uint |
| date_added | timestamp |
| test | uint |
+------------+-----------+
mysql> alter table rt drop column group_id;
mysql> desc rt;
+------------+-----------+
| Field | Type |
+------------+-----------+
| id | bigint |
| text | field |
| date_added | timestamp |
| test | uint |
+------------+-----------+
mysql> alter table rt add column title text indexed;
mysql> desc rt;
+------------+-----------+------------+
| Field | Type | Properties |
+------------+-----------+------------+
| id | bigint | |
| text | text | indexed |
| title | text | indexed |
| date_added | timestamp | |
| test | uint | |
+------------+-----------+------------+
mysql> alter table rt add column title text attribute;
mysql> desc rt;
+------------+-----------+------------+
| Field | Type | Properties |
+------------+-----------+------------+
| id | bigint | |
| text | text | indexed |
| title | text | indexed |
| date_added | timestamp | |
| test | uint | |
| title | string | |
+------------+-----------+------------+
mysql> alter table rt drop column title;
mysql> desc rt;
+------------+-----------+------------+
| Field | Type | Properties |
+------------+-----------+------------+
| id | bigint | |
| text | text | indexed |
| title | text | indexed |
| date_added | timestamp | |
| test | uint | |
+------------+-----------+------------+
mysql> alter table rt drop column title;
mysql> desc rt;
+------------+-----------+------------+
| Field | Type | Properties |
+------------+-----------+------------+
| id | bigint | |
| text | text | indexed |
| date_added | timestamp | |
| test | uint | |
+------------+-----------+------------+ALTER RTINDEX index ft_setting='value'[, ft_setting2='value']You can also use ALTER to modify full-text settings of your index in RT mode. Just remember that it doesn't affect existing documents, it only affects new ones. Take a look at the example where we:
- create an index with a full-text field and
charset_tablethat allows only 3 searchable characters:a,bandc. - then we insert document 'abcd' and find it by query
abcd, thedjust gets ignored since it's not in thecharset_tablearray - then we understand, that we want
dto be searchable too, so we add it with help ofALTER - but the same query
where match('abcd')still says it searched byabc, because the existing document remembers previous contents ofcharset_table - then we add another document
abcdand search byabcdagain - now it finds the both documents and
show metasays it used two keywords:abc(to find the old document) andabcd(for the new one).
- Example
Example
📋
mysql> create table rt(title text) charset_table='a,b,c';
mysql> insert into rt(title) values('abcd');
mysql> select * from rt where match('abcd');
+---------------------+-------+
| id | title |
+---------------------+-------+
| 1514630637682688054 | abcd |
+---------------------+-------+
mysql> show meta;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| total | 1 |
| total_found | 1 |
| time | 0.000 |
| keyword[0] | abc |
| docs[0] | 1 |
| hits[0] | 1 |
+---------------+-------+
mysql> alter table rt charset_table='a,b,c,d';
mysql> select * from rt where match('abcd');
+---------------------+-------+
| id | title |
+---------------------+-------+
| 1514630637682688054 | abcd |
+---------------------+-------+
mysql> show meta
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| total | 1 |
| total_found | 1 |
| time | 0.000 |
| keyword[0] | abc |
| docs[0] | 1 |
| hits[0] | 1 |
+---------------+-------+
mysql> insert into rt(title) values('abcd');
mysql> select * from rt where match('abcd');
+---------------------+-------+
| id | title |
+---------------------+-------+
| 1514630637682688055 | abcd |
| 1514630637682688054 | abcd |
+---------------------+-------+
mysql> show meta;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| total | 2 |
| total_found | 2 |
| time | 0.000 |
| keyword[0] | abc |
| docs[0] | 1 |
| hits[0] | 1 |
| keyword[1] | abcd |
| docs[1] | 1 |
| hits[1] | 1 |
+---------------+-------+ALTER RTINDEX index RECONFIGUREALTER can also reconfigure an RT index in plain mode, so that new tokenization, morphology and other text processing settings from the configuration file take effect for new documents. Note, that the existing document will be left intact. Internally, it forcibly saves the current RAM chunk as a new disk chunk and adjusts the index header, so that new documents are tokenized using the updated full-text settings.
- Example
Example
📋
mysql> show index rt settings;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| settings | |
+---------------+-------+
1 row in set (0.00 sec)
mysql> alter rtindex rt reconfigure;
Query OK, 0 rows affected (0.00 sec)
mysql> show index rt settings;
+---------------+----------------------+
| Variable_name | Value |
+---------------+----------------------+
| settings | morphology = stem_en |
+---------------+----------------------+
1 row in set (0.00 sec)