MYSQL vs PgSQL

MySQL 建库 -> 建表 PgSQL 建库 -> Schema -> 建表

创建库

MySQL

CREATE DATABASE ufs 
       DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;

PgSQL

CREATE DATABASE ufs
       WITH OWNER postgres
       ENCODING 'UTF8'
       TEMPLATE template0;

创建Schema

MySQL无，PgSQL有

CREATE SCHEMA IF NOT EXISTS ufs_user_schema;

创建表

MySQL

----------------------MySQL DEMO------------------------------
-- 创建用户表
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    name VARCHAR(100) NOT NULL CHECK(LENGTH(name) BETWEEN 2 AND 50) COMMENT '姓名',
    email VARCHAR(255) NOT NULL UNIQUE CHECK (email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$') COMMENT '邮箱',
    user_metadata JSON NOT NULL DEFAULT ('{}') COMMENT '用户元数据JSON对象',
    create_at TIMESTAMP  NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    is_active TINYINT(1) NOT NULL DEFAULT 1 COMMENT '用户是否激活'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户表';
-- 修改注释
ALTER TABLE users MODIFY name VARCHAR(100) NOT NULL COMMENT '新备注：用户昵称，2-50个字符';
----------------------------------------------------

PgSQL

----------------------PostgreSQL DEMO------------------------------
-- 创建用户表
CREATE TABLE IF NOT EXISTS users (
    id   SERIAL        PRIMARY KEY,
    name  VARCHAR(100) NOT NULL CHECK(LENGTH(name) >= 2 AND LENGTH(name) <= 50),
    email  VARCHAR(255) NOT NULL UNIQUE CHECK(email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
    user_metadata JSONB  NOT NULL DEFAULT '{}'::JSONB,
    create_at  TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    is_active  BOOLEAN NOT NULL DEFAULT TRUE
);
COMMENT ON TABLE users IS '用户表';
COMMENT ON COLUMN users.id IS '用户ID';
COMMENT ON COLUMN users.name IS '姓名';
COMMENT ON COLUMN users.email IS '邮箱';
COMMENT ON COLUMN users.user_metadata IS '用户元数据JSONB对象';
COMMENT ON COLUMN users.create_at IS '创建时间';
COMMENT ON COLUMN users.is_active IS '用户是否激活';    
-- 修改注释
COMMENT ON COLUMN users.name IS '新备注：用户昵称，2-50个字符';
----------------------------------------------------