# CONNECT Create Table Options Create Table statements for “CONNECT” tables are standard MariaDB create statements specifying`engine=CONNECT`. There are a few additional table and column options specific to CONNECT. ### Table Options | Table Option | Type | Description | | ---------------- | ------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | AVG\_ROW\_LENGTH | Integer | Can be specified to help CONNECT estimate the size of a variable record table length. | | BLOCK\_SIZE | Integer | The number of rows each block of a [FIX](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-dos-and-fix-table-types), [BIN](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-bin-table-type), [DBF](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-dbf-table-type), or [VEC](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-vec-table-type) table contains. For an [ODBC](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-odbc-table-type-accessing-tables-from-another-dbms) table this is the RowSet size option. For a [JDBC](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-jdbc-table-type-accessing-tables-from-another-dbms) table this is the fetch size. | | CATFUNC | String | The catalog function used by a [catalog table](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-table-types-catalog-tables). | | COLIST | String | The column list of [OCCUR](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-occur-table-type) tables or $project of [MONGO](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-mongo-table-type) tables. | | COMPRESS | Number | 1 or 2 if the data file is g-zip compressed. Defaults to 0. Before CONNECT 1.05.0001, this was boolean, and true if the data file is compressed. | | CONNECTION | String | Specifies the connection of an [ODBC](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-odbc-table-type-accessing-tables-from-another-dbms), [JDBC](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-jdbc-table-type-accessing-tables-from-another-dbms) or [MYSQL](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-mysql-table-type-accessing-mysqlmariadb-tables) table. | | DATA\_CHARSET | String | The character set used in the external file or data source. | | DBNAME | String | The target database for [ODBC](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-odbc-table-type-accessing-tables-from-another-dbms), [JDBC](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-jdbc-table-type-accessing-tables-from-another-dbms), [MYSQL](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-mysql-table-type-accessing-mysqlmariadb-tables), [catalog](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-table-types-catalog-tables), and [PROXY](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-proxy-table-type) based tables. The database concept is sometimes known as a schema. | | ENGINE | String | Must be specfied as CONNECT. | | ENDING | Integer | End of line length. Defaults to 1 for Unix/Linux and 2 for Windows. | | FILE\_NAME | String | The file (path) name for all table types based on files. Can be absolute or relative to the current data directory. If not specified, this is an [Inward table](https://mariadb.com/docs/server/server-usage/storage-engines/connect-table-types/inward-and-outward-tables#inward-tables) and a default value is used. | | FILTER | String | To filter an external table. Currently MONGO tables only. | | HEADER | Integer | Applies to [CSV](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-csv-and-fmt-table-types), [VEC](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-vec-table-type), and HTML files. Its meaning depends on the table type. | | HTTP | String | The HTTP of the client of REST queries. From [Connect 1.06.0010](https://mariadb.com/docs/server/server-usage/storage-engines/connect). | | HUGE | Boolean | To specify that a table file can be larger than 2GB. For a [MYSQL](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-mysql-table-type-accessing-mysqlmariadb-tables) table, prevents the result set from being stored in memory. | | LRECL | Integer | The file record size (often calculated by default). | | MAPPED | Boolean | Specifies whether file mapping is used to handle the table file. | | MODULE | String | The (path) name of the DLL or shared lib implementing the access of a non-standard ([OEM](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-table-types-oem)) table type. | | MULTIPLE | Integer | Used to specify multiple file tables. | | OPTION\_LIST | String | Used to specify all other options not yet directly defined. | | QCHAR | String | Specifies the character used for quoting some fields of a [CSV](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-csv-and-fmt-table-types) table or the identifiers of an [ODBC](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-odbc-table-type-accessing-tables-from-another-dbms)/[JDBC](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-jdbc-table-type-accessing-tables-from-another-dbms) tables. | | QUOTED | Integer | The level of quoting used in [CSV](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-csv-and-fmt-table-types) table files. | | READONLY | Boolean | True if the data file must not be modified or erased. | | SEP\_CHAR | String | Specifies the field separator character of a [CSV](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-csv-and-fmt-table-types) or [XCOL](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-xcol-table-type) table. Also, used to specify the Jpath separator for [JSON tables](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-json-table-type). | | SEPINDEX | Boolean | When true, indexes are saved in separate files. | | SPLIT | Boolean | True for a [VEC](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-vec-table-type) table when all columns are in separate files. | | SRCDEF | String | The source definition of a table retrieved via [ODBC](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-odbc-table-type-accessing-tables-from-another-dbms), [JDBC](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-jdbc-table-type-accessing-tables-from-another-dbms) or the MySQL API or used by a [PIVOT](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-pivot-table-type) table. | | SUBTYPE | String | The subtype of an [OEM](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-table-types-oem) table type. | | TABLE\_LIST | String | The comma separated list of TBL table sub-tables. | | TABLE\_TYPE | String | The external table [type](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-table-types-overview): [DOS](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-dos-and-fix-table-types), [FIX](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-dos-and-fix-table-types), [BIN](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-bin-table-type), [CSV](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-csv-and-fmt-table-types), [FMT](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-csv-and-fmt-table-types), [XML](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-xml-table-type), [JSON](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-json-table-type), [INI](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-ini-table-type), [DBF](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-dbf-table-type), [VEC](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-vec-table-type), [ODBC](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-odbc-table-type-accessing-tables-from-another-dbms), [JDBC](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-jdbc-table-type-accessing-tables-from-another-dbms), [MYSQL](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-mysql-table-type-accessing-mysqlmariadb-tables), [TBL](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-tbl-table-type-table-list), [PROXY](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-proxy-table-type), [XCOL](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-xcol-table-type), [OCCUR](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-occur-table-type), [PIVOT](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-pivot-table-type), [ZIP](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-zipped-file-tables), [VIR](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-table-types-vir), [DIR](https://mariadb.com/docs/server/server-usage/storage-engines/connect-table-types/connect-table-types-special-virtual-tables#dir-type), [WMI](https://mariadb.com/docs/server/server-usage/storage-engines/connect-table-types/connect-table-types-special-virtual-tables#windows-management-instrumentation-table-type-wmi), [MAC](https://mariadb.com/docs/server/server-usage/storage-engines/connect-table-types/connect-table-types-special-virtual-tables#mac-address-table-type-mac), and [OEM](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-table-types-oem). Defaults to [DOS](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-dos-and-fix-table-types), [MYSQL](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-mysql-table-type-accessing-mysqlmariadb-tables), or [PROXY](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-proxy-table-type) depending on what options are used. | | TABNAME | String | The target table or node for [ODBC](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-odbc-table-type-accessing-tables-from-another-dbms), [JDBC](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-jdbc-table-type-accessing-tables-from-another-dbms), [MYSQL](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-mysql-table-type-accessing-mysqlmariadb-tables), [PROXY](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-proxy-table-type), or [catalog tables](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-table-types-catalog-tables); or the top node name for [XML](https://mariadb.com/docs/server/server-usage/storage-engines/connect/connect-table-types/connect-xml-table-type) tables. | | URI | String | The URI of a REST request.. From [Connect 1.06.0010](https://mariadb.com/docs/server/server-usage/storage-engines/connect). | | XFILE\_NAME | String | The file (path) base name for table index files. Can be absolute or relative to the data directory. Defaults to the file name. | | ZIPPED | Boolean | True if the table file(s) is/are zipped in one or several zip files. | All integers in the above table are unsigned big integers. Because CONNECT handles many table types; many table type specific options are not in the above list and must be entered using the `OPTION_LIST` option. The syntax to use is: ``` ... option_list='opname1=opvalue1,opname2=opvalue2...' ``` Be aware that until Connect 1.5.5, no blanks should be inserted before or after the '`=`' and '`,`' characters. The option name is all that is between the start of the string or the last '`,`' character and the next '`=`' character, and the option value is all that is between this '`=`' character and the next '`,`'\ or end of string. For instance: ``` option_list='name=TABLE,coltype=HTML,attribute=border=1;cellpadding=5,headattr=bgcolor=yellow'; ``` This defines four options, '`name`', '`coltype`', '`attribute`', and '`headattr`'; with values '`TABLE`', '`HTML`',\ '`border=1;cellpadding=5`', and '`bgcolor=yellow`', respectively. The only restriction is that values cannot contain commas, but they can contain equal signs. ### Column Options | Column Option | Type | Description | | ------------- | ------- | ---------------------------------------------------------- | | DATE\_FORMAT | String | The format indicating how a date is stored in the file. | | DISTRIB | Enum | “scattered”, “clustered”, “sorted” (ascending). | | FIELD\_FORMAT | String | The column format for some table types. | | FIELD\_LENGTH | Integer | Set the internal field length for DATE columns. | | FLAG | Integer | An integer value whose meaning depends on the table type. | | JPATH | String | The Json path of JSON table columns. | | MAX\_DIST | Integer | Maximum number of distinct values in this column. | | SPECIAL | String | The name of the SPECIAL column that set this column value. | | XPATH | String | The XML path of XML table columns. | * The `MAX_DIST` and `DISTRIB` column options are used for block indexing. * All integers in the above table are unsigned big integers. * JPATH and XPATH were added to make CREATE TABLE statements more readable, but they do the same thing as FIELD\_FORMAT and any of them can be used with the same result. ### Index Options | Index Option | Type | Description | | ------------ | ------- | --------------------------- | | DYNAM | Boolean | Set the index as “dynamic”. | | MAPPED | Boolean | Use index file mapping. | **Note 1:** Creating a CONNECT table based on file does not erase or create the file if the file name is specified in the `CREATE TABLE` statement ([“outward”](https://mariadb.com/docs/server/server-usage/storage-engines/connect-table-types/inward-and-outward-tables#outward-tables) table). If the file does not exist, it are populated by subsequent `INSERT` or `LOAD` statements or by the “AS select statement” of the [CREATE TABLE](https://mariadb.com/docs/server/server-usage/tables/create-table)\ statement. Unlike the CSV engine, CONNECT easily permits the creation of tables based on already existing files, for instance files made by other applications. However, if the file name is not specified, a file with a name defaulting to`tablename.tabletype` are created in the data directory ([“inward”](https://mariadb.com/docs/server/server-usage/storage-engines/connect-table-types/inward-and-outward-tables#inward-tables) table). **Note 2:** Dropping a CONNECT table is done with a standard `DROP` statement. For [outward tables](https://mariadb.com/docs/server/server-usage/storage-engines/connect-table-types/inward-and-outward-tables#inward-tables), this drops only the CONNECT table definition but does not erase the corresponding data file and index files. Use `DELETE` or`TRUNCATE` to do so. This is contrary to data and index files of [inward tables](https://mariadb.com/docs/server/server-usage/storage-engines/connect-table-types/inward-and-outward-tables#inward-tables) are erased on `DROP` like for other MariaDB engines. *This page is licensed: GPLv2* {% @marketo/form formId="4316" %}