CREATE TABLE t(id int) ENGINE = CSV
ALTER TABLE
. e.g. ALTER TABLE t ENGINE = CSV
default_storage_engine
other than InnoDB. e.g. SET default_storage_engine = CSV
foo
in WITH foo AS (SELECT * FROM public.pokes) SELECT * FROM foo
.
Object Name | Fully qualified |
---|---|
table_name | no |
schema_name.table_name | yes |
database_name.schema_name.table_name | yes |
Table Naming Convention
uses regular expression as the format for naming pattern, and also limits the naming max length. The default maximum length is 64 characters. Length limit does not support PostgreSQL.
Name | Regular Expression |
---|---|
snake_lower_case | ^[a-z]+(_[a-z]+)*$ |
CamelCase | ^([A-Z][a-z]*)+$ |
lowerCamelCase | ^[a-z]+([A-Z][a-z]*)*$ |
kebab-case | ^[a-z]+(-[a-z]+)*$ |
CREATE TABLE
statementsALTER TABLE RENAME TO
statementsRENAME TABLE
statementsColumn Naming Convention
uses regular expression format for naming pattern, and also limits the naming max length. The default maximum length is 64 characters. Length limit does not support PostgreSQL.
Name | Regular Expression |
---|---|
snake_lower_case | ^[a-z]+(_[a-z]+)*$ |
CamelCase | ^([A-Z][a-z]*)+$ |
lowerCamelCase | ^[a-z]+([A-Z][a-z]*)*$ |
kebab-case | ^[a-z]+(-[a-z]+)*$ |
CREATE TABLE
statementsALTER TABLE RENAME COLUMN
statementsALTER TABLE ADD COLUMNS
statementsALTER TABLE CHANGE COLUMN
statementsAuto-increment Column Naming Convention
uses regular expression format for naming pattern, and also limits the naming maximum length. The default maximum length is 64 characters.
Name | Regular Expression |
---|---|
id | ^id$ |
CREATE TABLE
statementsALTER TABLE
statementsIndex Naming Convention
uses template
format. Specifically, the template
is an extended regular expression. The rest follows the regular expression rules except the part with curly braces.
For example, ^idx_{{table}}_{{column_list}}$
is a template
where {{table}}
is the table name and {{column_list}}
is the list of the column name. So for index on user(id, name)
, the legal name is idx_user_id_name
.
It also limits the naming max length. The default maximum length is 64 characters. Length limit does not support PostgreSQL.
Index Naming Convention
rule is only valid for index, which means it does NOT work for unique key, foreign key and primary key.
Also see primary key naming, unique key naming convention and foreign key naming convention.CREATE TABLE
statementsALTER TABLE RENAME INDEX
statementsALTER TABLE ADD CONSTRAINT
statementsCREATE INDEX
statementsPrimary Key Naming Convention
uses template
format. Specifically, the template
is an extended regular expression. The rest follows the regular expression rules except the part with curly braces.
For example, ^pk_{{table}}_{{column_list}}$
is a template
where {{table}}
is the table name and {{column_list}}
is the list of the column name. So for primary key on user(id, name)
, the legal name is pk_user_id_name
.
Primary Key Naming Convention
rule is only valid for primary key, which means it does NOT work for unique key, foreign key and normal index.
Also see index naming convention, unique key naming convention and foreign key naming convention.CREATE TABLE
statementsALTER INDEX RENAME TO
statementsALTER TABLE ADD CONSTRAINT
statementsUnique Key Naming Convention
uses template
format. Specifically, the template
is an extended regular expression. The rest follows the regular expression rules except the part with curly braces.
For example, ^uk_{{table}}_{{column_list}}$
is a template
where {{table}}
is the table name and {{column_list}}
is the list of the column name. So for unique key on user(id, name)
, the legal name is uk_user_id_name
.
It also limits the naming max length. The default maximum length is 64 characters. Length limit does not support PostgreSQL.
Unique Key Naming Convention
rule is only valid for unique key, which means it does NOT work for index, foreign key and primary key.
Also see index naming convention, primary key naming convention and foreign key naming convention.CREATE TABLE
statementsALTER TABLE RENAME INDEX
statementsALTER TABLE ADD CONSTRAINT
statementsCREATE INDEX
statementsForeign Key Naming Convention
uses template
format. Specifically, the template
is an extended regular expression. The rest follows the regular expression rules except the part with curly braces.
For example, ^fk_{{referencing_table}}_{{referencing_column}}_{{referenced_table}}_{{referenced_column}}$
is a template
where {{referencing_table}}
is the name of the referencing table, {{referencing_column}}
is the list of the referencing column name, {{referenced_table}}
is the name of the referenced table and {{referenced_column}}
is the list of the referencing column name. So for unique key on user(id, name)
, the legal name is uk_user_id_name
.
It also limits the naming max length. The default maximum length is 64 characters. Length limit does not support PostgreSQL.
Foreign Key Naming Convention
rule is only valid for foreign key, which means it does NOT work for index, unique key and primary key.
Also see index naming convention, primary key naming convention and unique key naming convention.CREATE TABLE
statementsALTER TABLE ADD CONSTRAINT
statementsDisallow keywords as table names
above.
SELECT *
introduces additional performance cost or ambiguous semantics.
For scenarios where all columns are not required, you should SELECT the columns you need to avoid getting unneeded data.
For scenarios where all columns are required, you should list all column names to avoid semantic ambiguity. Otherwise, the data consumer cannot know the column information. And SELECT *
may bring additional modifications and errors when modifying the table schema.
SELECT *
.
WHERE 1=1
to remind yourself of the consequences of that action.
INSERT
statementsUPDATE
statementsDELETE
statementsUPDATE
statementsDELETE
statementsALTER TABLE
statements for the same table.
CREATE TABLE
statementsALTER TABLE
statementsINSERT INTO table_t(id, name) VALUES(...)
.
INSERT
statementsORDER BY RAND()
clause is not necessary for INSERT statements.
INSERT
statementsINSERT INTO ... VALUES(...)
statements, Bytebase checks the count of value list.INSERT INTO ... SELECT ...
statements, Bytebase runs EXPLAIN
statements for them and check the rows in EXPLAIN
statement results.UPDATE
or DELETE
exceed the limit.
UPDATE
and DELETE
statements, Bytebase runs EXPLAIN
statements for them and check the rows in EXPLAIN
statement results.
EXPLAIN
statements. Specifically, Bytebase checks:
INSERT
statementsUPDATE
statementsDELETE
statementsALTER TABLE ADD COLUMN
statements.
ALTER TABLE ADD CONSTRAINT
statements.
ALTER TABLE ADD CONSTRAINT
statements.
FOREIGN KEY
constraints are impossible to maintain once your data grows and is split over multiple database servers. This typically happens when you introduce functional partitioning/sharding and/or horizontal sharding.
CREATE TABLE
statement with foreign keyALTER TABLE ADD CONSTRAINT FOREIGN KEY
statement_del
suffix.
DROP TABLE
statementsCREATE TABLE
statementsALTER TABLE
statementsCREATE TABLE
statementsALTER TABLE
statementsid
as identification and the primary key for each table or need created_ts
and updated_ts
to record creation and modification times.
You can customize which columns are required.
CREATE TABLE
statementsALTER TABLE
statementsALTER TABLE
statementsCREATE TABLE
statementsALTER TABLE
statementsALTER TABLE CHANGE COLUMN
statements.
ALTER TABLE
statementsCREATE TABLE
statementsALTER TABLE
statementsCREATE TABLE
statementsALTER TABLE
statementsCREATE TABLE
statementsALTER TABLE
statementsCREATE TABLE
statementsALTER TABLE
statementsCREATE TABLE
statementsALTER TABLE
statementsCREATE TABLE
statementsALTER TABLE
statementsCREATE TABLE
statementsNOW()
, CURRENT_TIME()
and CURRENT_TIMESTAMP()
columns.
DEFAULT NOW()
ON UPDATE NOW()
created_ts
and updated_ts
column with DEFAULT NOW()
.updated_ts
column with ON UPDATE NOW()
CREATE TABLE
statementsALTER TABLE
statementsCREATE TABLE
statementsALTER TABLE
statementsALTER TABLE DROP COLUMN
statementsclock_timestamp()
, update each row with the value at the time of ALTER TABLE ADD COLUMN
execution. This can lead to lengthy updates and potential performance issues.
ALTER TABLE ADD COLUMN
statements.
CREATE TABLE
statementsALTER TABLE
statementsCREATE INDEX
statementsCREATE TABLE
statementsALTER TABLE
statementsCREATE INDEX
statementsCREATE TABLE
statementsALTER TABLE
statementsCREATE TABLE
statementsALTER TABLE
statementsCREATE INDEX
statementsCREATE TABLE
statementsALTER TABLE
statementsCREATE INDEX
statementsCREATE TABLE
statementsALTER TABLE ADD CONSTRAINT
statementsCREATE INDEX
statementsDROP DATABASE
statementsCREATE TABLE
statementsALTER TABLE
statementsCREATE TABLE
statementsALTER TABLE
statementsCOMMENT ON
statements.