Skip to main content

CREATE A TABLE

After we've constructed the database, we may add tables to it. Tables can only be created by the database owner or other accounts having access permissions to the database. To build a table, we can use the CREATE TABLE statement. To store our book information, we can use the following SQL command to create a book table in the bookshop database.

SQL Example

CREATE TABLE bookshop.book (
	id INT(11) PRIMARY KEY COMMENT 'id',
	name VARCHAR(255) COMMENT 'name of book',
	category VARCHAR(255) COMMENT 'book category (eg:history,cartoon..)',
	author VARCHAR(255) COMMENT 'author',
	public_at INT(11) COMMENT 'public time',
	stock INT(11) COMMENT 'stock of book',
	price INT(11) COMMENT 'price',
	_tx_id VARCHAR(255),
    KEY `name_ind` (name),
    KEY `name_author_ind` (name, author)
) ENGINE=standard;

Using the SDK​

We give Python and JavaScript SDK versions, making it simple to construct our data tables. The Python SDK is used to create a table for books, as shown below.

To construct a table, utilize the Python SDK's create_table() method.

sql = """
CREATE TABLE bookshop.book (
	id INT(11) PRIMARY KEY COMMENT 'id',
	name VARCHAR(255) COMMENT 'name of book',
	category VARCHAR(255) COMMENT 'book category (eg:history,cartoon..)',
	author VARCHAR(255) COMMENT 'author',
	public_at INT(11) COMMENT 'public time',
	stock INT(11) COMMENT 'stock of book',
	price INT(11) COMMENT 'price',
	_tx_id VARCHAR(255)
) ENGINE=standard;
"""
db = client.db(mk)
db.create_table(sql)

Our book table primarily contains the following data: primary key id, book name name, book category category, book author author, book publication time public_at, current stock stock, book price price, _tx_id column is used to record transaction ID information. The ENGINE=standard specifies that our data table stores data utilizing the standard engine. More information about _tx_id and supported engine types is provided below.

[!TIP] > Autofill TX: If a _tx_id column with a varchar data type is defined when establishing a table, the current Transaction ID will be automatically populated when an INSERT operation is done. Indexes for this column, however, will not be built by default.

Types of Engines​

To construct data tables, we can now use two sorts of engines:

  1. standard: This engine is widely used to construct data tables since it supports structured data storage and querying better

  2. full_text: Because it has greater full-text indexing capabilities, this engine is better suited for producing data tables in full-text indexing scenarios.

Standard Engine​

The standard engine supports structured data storage and querying better. It allows you to make secondary indexes, unique indexes, and composite indexes. It does not, however, enable auto-incrementing primary keys. The ENGINE=standard in our construct table statement in the preceding example indicates that we used the standard engine to generate the book table.

Full_text Engine​

Index Types Index Types The full_text keyword can be used to define a Full-Text index, and the WITH PARSER option can be used to specify the parser.

NameDescription
keywordKeyword, no word segmentation is applied, suitable for exact match scenarios.
standardStandard word segmentation, suitable for English word segmentation scenarios.
jiebaJieba word segmentation, suitable for Chinese word segmentation scenarios.

SQL Example

CREATE TABLE bookshop.book (
	id INT(11) PRIMARY KEY COMMENT 'id',
	name VARCHAR(255) COMMENT 'name of book',
	category VARCHAR(255) COMMENT 'book category (eg:history,cartoon..)',
	author VARCHAR(255) COMMENT 'author',
	public_at INT(11) COMMENT 'public time',
	stock INT(11) COMMENT 'stock of book',
	price INT(11) COMMENT 'price',
	KEY idx_author (author),
    FULLTEXT(name) WITH PARSER standard,
) ENGINE=full_text;
Creating A Table Using SDK​
sql = """
CREATE TABLE bookshop.book (
	id INT(11) PRIMARY KEY COMMENT 'id',
	name VARCHAR(255) COMMENT 'name of book',
	category VARCHAR(255) COMMENT 'book category (eg:history,cartoon..)',
	author VARCHAR(255) COMMENT 'author',
	public_at INT(11) COMMENT 'public time',
	stock INT(11) COMMENT 'stock of book',
	price INT(11) COMMENT 'price',
	KEY idx_author (author),
    FULLTEXT(name) WITH PARSER standard,
) ENGINE=full_text;
"""
db = client.db(mk)
db.create_table(sql)