Some principles of the database and table specifications
![Github of Anigkus Some principles of the database and table specifications](assets/images/some-principles-of-the-database-and-table-specifications/figure-1.jpeg)
In order to standardize database operations, unify team communication methods, facilitate subsequent project maintenance, improve product code quality, and guide software developers to write concise, maintainable, reliable, testable, efficient, and portable SQL, refer to company conditions and industry database specification, so this specification was written and codified.
Term
-
Principle
: The guiding ideology that must be adhered to when programming.
-
Rules
: conventions that must be followed when programming.
-
Suggestions
: Conventions that must be considered when programming.
Explanation: Provide necessary explanation of this principle/rule/recommendation.
Examples: Give examples of this principle/rule/suggestion from both positive and negative aspects. Further reading materials: Reference materials for further reading.
Specification
Principle: Columns in timestamp format are not allowed
Explanation:
Timestrap has three major problems: performance problems, implicit problems, and time ranges.
Example:
created_time` timestrap NOT NULL COMMENT 'Created Time',
Principle: Coding format and Collate rules for databases & tables
Explanation:
Encoding format: utf8mb4
Collate rule: utf8mb4_unicode_ci
Example:
Database
CREATE DATABASE `t_name` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ;
Table
CREATE TABLE `d_name`.`t_name` (
`id` bigint(20) NOT NULL COMMENT 'primary id',
`is_deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'logical deletion, 0: not deleted, 1: deleted',
`created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'created time',
`updated_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'updated time',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='table comment';
Principle: Tables, columns, fields, and indexes must have relevant comments
Explanation:
Table COMMENT ‘xxx’, column COMMENT ‘xxx’, index COMMENT ‘xxx’
Normal indexes: non-unique indexes are named using IDX_field_name
Unique indexes: Unique indexes are named using UNI_field_name
Example:
CREATE TABLE `d_name`.`t_name` (
`id` bigint(20) NOT NULL COMMENT 'primary id',
`application_id` bigint(20) NOT NULL COMMENT 'application id',
`is_deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'logical deletion, 0: not deleted, 1: deleted',
`created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'created time',
`updated_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'updated time',
PRIMARY KEY (`id`),
KEY `IDX_application_id-is_deleted` (`application_id`,`is_deleted`) COMMENT 'logical delete index'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='table comment';
Principle: Add created_time and updated_time fields to each table
Explanation:
`created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'created time',
`updated_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'updated time',
Example:
CREATE TABLE `d_name`.`t_name` (
`id` bigint(20) NOT NULL COMMENT 'primary id',
`created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'created time',
`updated_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'updated time',
PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='table comment';
Principle: Do not insert position markers when adding new columns in the data table
Explanation:
FIRST
:Indicates that the addition is before a column
AFTER
:Indicates that the addition is after a column
Example:
Error
ALTER TABLE `d_name`.`t_name`
ADD COLUMN `is_deleted` VARCHAR(45) NULL AFTER `updated_by` COMMENT 'logical deletion, 0: not deleted, 1: deleted';
Right
ALTER TABLE `d_name`.`t_name`
ADD COLUMN `is_deleted` VARCHAR(45) NULL COMMENT 'logical deletion, 0: not deleted, 1: deleted';
Principle: Updating database records requires backup statements
Explanation:
Which conditional data columns are updated, which conditional columns are backed up
The data of which conditions are updated, the data of which conditions are backed up
Example:
For example, if I update the column created_time based on id (1, 2, 3), then only the created_time column of id (1, 2, 3) needs to be backed up.
UPDATE `d_name`.`t_name` SET created_time=NOW() WHERE id in(1,2,3);
SELECT id,created_time FROM `d_name`.`t_name` WHERE id in(1,2,3) INTO OUTFILE '/tmp/mysql/d_name-t_name-YYYYMMDD-num.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
Principle: It is not allowed to delete fields that already exist in the table
Explanation:
Cannot drop column using DROP COLUMN
Example:
ALTER TABLE `d_name`.`t_name`
DROP COLUMN `version`;
Principle: When making changes, submit attachments uniformly and separate DDL and DML statements into two files
Explanation:
DDL and DML statements are separated into two files
file name: library name-table name-change date (YYYYMMDD-1)-ddl.SQL, all are .SQL suffix
Unified encoding UTF-8, write the execution order in the remarks
The operations of adding, deleting and modifying must first backup the statement, refer to: (update database records must have backup statements)
Example:
Name:d_name-t_name-20200820-1-ddl.SQL
ALTER TABLE `d_name`.`t_name`
ADD COLUMN `is_deleted` VARCHAR(45) NULL COMMENT 'logical deletion, 0: not deleted, 1: deleted';
Name:d_name-t_name-20200820-1-dml.SQL
DELETE FROM `d_name`.`t_name` WHERE id= 1;
Principle: Must have a unique primary key index and auto increment
Explanation:
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'primary id'
Example:
CREATE TABLE `d_name`.`t_name` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'primary id',
`created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'created time',
`updated_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'updated time',
PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='table comment';
Principle: The fields in the table cannot have NULL values of type CHAR
Explanation:
When ROW_FORMAT=COMPACT, the NULL value of CHAR type does not occupy space
When ROW_FORMAT=REDUNDANT, the NULL value of CHAR type takes up space
VARCHAR takes no storage space in either row format
Example:
CREATE TABLE `d_name`.`t_name` (
`id` bigint(20) NOT NULL COMMENT 'primary id',
`application_id` bigint(20) NOT NULL COMMENT 'application id',
`is_deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'logical deletion, 0: not deleted, 1: deleted',
`created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'created time',
`updated_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'updated time',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='table comment';
Principle: Do not use internal keywords as column names
Explanation:
For example:NAME,VALUE,SELECT,ALTER AND,DELETE, etc.,Refernces
Example:
Error:(delete column)
CREATE TABLE `d_name`.`t_name` (
`id` bigint(20) NOT NULL COMMENT 'primary id',
`application_id` bigint(20) NOT NULL COMMENT 'application id',
`delete` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'logical deletion, 0: not deleted, 1: deleted',
`created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'created time',
`updated_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'updated time',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='table comment';
Right:(delete column)
CREATE TABLE `d_name`.`t_name` (
`id` bigint(20) NOT NULL COMMENT 'primary id',
`application_id` bigint(20) NOT NULL COMMENT 'application id',
`is_deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'logical deletion, 0: not deleted, 1: deleted',
`created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'created time',
`updated_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'updated time',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='table comment';
Principle: Do not delete any column and table
Explanation:
For example:DROP table
For example:Alter table table_name drop column column_name;
Example:
Error:(Drop table & column)
DROP table tablea;
Alter table tablea drop column col;
Principle: Do not change any existing table names and column name
Explanation:
For example: ALTER TABLE old_table
RENAME AS new_table
;
For example: ALTER TABLE old_table
change old_column
new_columb
column_type;
Example:
Error
ALTER TABLE `old_table` RENAME AS `new_table`;
alter table `old_table` change `old_columnname` `new_columnname` varchar(20);
Example of backup table:
CREATE TABLE `test`.`user1` (
`id` int(11) NOT NULL,
`names` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='table user';
--Contains all data and structures (but not primary keys, indexes, etc.)
CREATE TABLE `test`.`user2` AS
(
SELECT *
FROM test.user1
);
-- Contains all structure and primary key information (but not data, etc.)
CREATE TABLE `test`.`user3` LIKE `test`.`user1`;
-- Insert original data
INSERT INTO `test`.`user3` SELECT * FROM `test`.`user1`;
-- Contains all data and structures (but not primary keys, indexes, etc.)
CREATE TABLE `test`.`user4` SELECT * FROM `test`.`user1`;
Principle: Delete row records in the table, the product does not specifically say that physical deletion is required, and logical identification is required to delete
Explanation:
`is_deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'logical deletion, 0: not deleted, 1: deleted',
Example:
CREATE TABLE `d_name`.`t_name` (
`id` bigint(20) NOT NULL COMMENT 'primary id',
`application_id` bigint(20) NOT NULL COMMENT 'application id',
`is_deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'logical deletion, 0: not deleted, 1: deleted',
`created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'created time',
`updated_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'updated time',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='table comment';