The design specifications and constraints of OceanBase Database cover object naming, structure design, character sets, connections, comments, ORM, and exception handling. These guidelines help ensure efficiency, consistency, and maintainability in database design and management. For more information, see Database design specifications and practices.
Object naming conventions
MySQL mode: Table names consist of lowercase letters, numbers, and underscores. They must start with a lowercase letter and end with a lowercase letter or number. The length must be at least 3 and at most 64 characters. Avoid using keywords or reserved words in MySQL mode.
Oracle mode: Table names consist of uppercase letters, numbers, and underscores. They must start with an uppercase letter and end with an uppercase letter or number. The length must be at least 3 and at most 64 characters. Avoid using keywords or reserved words in Oracle mode.
Temporary tables
Temporary tables should start with tmp_ to avoid confusion with regular tables.
Object structure design specifications
Table design: Minimize data redundancy and estimate the table structure rationally to ensure data consistency and integrity. Select an appropriate SQL data type for each column. Choose to create a non-partitioned table or a partitioned table based on business requirements.
Row length: Maximum 1.5 MB.
Number of columns: Maximum 512 columns.
Number of indexes: Maximum 128 indexes.
Number of partitions: Oracle mode supports up to 65,536 partitions, and MySQL mode supports up to 8,192 partitions.
Character set specifications
- Supports multiple character sets, such as UTF8 and GBK.
- In Oracle mode, UTF16 is not allowed as the database character set.
Database connection specifications
- Database connections should follow security specifications and avoid using default usernames and passwords.
- It is recommended to use encrypted connections (such as SSL/TLS) to protect data transmission security.
Comments
- Add clear comments to SQL statements and database objects (such as tables, columns, and indexes) to facilitate understanding and maintenance.
- Comments should be concise and describe the functionality and purpose of the object.
ORM conventions
- When using ORM (object relational mapping) tools, follow database design specifications to ensure that the generated SQL statements are efficient and meet business requirements.
- Avoid using native SQL in ORM unless there is a special need.
Exception handling
- For Oracle-compatible mode tenants: Error codes, SQLSTATE, and error messages should be as consistent with those of Oracle databases as possible. In distributed environments, the error mapping for ORA-00600 is unique.
- Error message format: SQL errors start with ORA, and stored procedure errors start with PLS. During development, capture and handle database exceptions to ensure system stability and data integrity.