A database table is a collection of two-dimensional arrays and represents and stores the relationship between database objects. This topic describes database objects related to database tables and the use of database tables, such as table storage, table compression, table groups, and temporary tables.
In a database, a table is the basic unit for data organization. A table has rows and columns. For example, a table named Author is created to store information about authors. Each column stores a specific attribute of all authors, such as their names. Each row contains all attributes of a specific author, such as the first name, last name, and address of the author. The number of columns of a database table is usually specified when the table is created. Columns are identified by their name. The number of rows can be updated anytime after the table is created.
Tables are divided into normal tables and temporary tables. For OceanBase Database in MySQL mode, a temporary table only exists in the session where it is created. All temporary tables created in a session are automatically dropped when the session ends. For example, a temporary table created in sess#1 is invisible to sess#2.
You can create temporary tables of the same name in different sessions. The temporary tables can also have the same name as an existing normal table. However, you cannot create views based on temporary tables. If a temporary table and a normal table have the same name, the SHOW CREATET TABLE, DESC, and DML operations act only on the temporary table, and the normal table is hidden.
Columns
In a database, a column stores the values of a specific attribute. The name of the attribute is used as the column name. Apart from the column name, information about a column also includes the data type and maximum length (precision).
OceanBase Database in MySQL mode supports three types of columns: regular columns, virtual columns (also called generated columns), and auto-increment columns.
Virtual columns do not provide physical storage space as regular columns do. Values in a virtual column are calculated by expressions or functions defined for the column.
Auto-increment columns satisfy the following rules:
They are globally unique across multiple partitions.
The column number increases in sequence within a statement.
Their generated values are larger than the values inserted by users.
Rows
In a database, a row stores the values of all columns of a specific record. Simply put, a database consists of columns and rows. Each row in a table stores a set of related data in the same structure.
For example, a table is created to store information about companies. Each row contains a set of data about a specific company. Each column contains data about a specific attribute of all companies, such as the company name, address, or tax number.