mysql - 例子 - 《李俊扬的技术分享》

sql 查询

例子一

# 创建用户表
CREATE TABLE `user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`password` varchar(20) NOT NULL,
`username` varchar(100) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `user_username_uindex` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

# i18n 语言表
CREATE TABLE `i18n_config` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `type` varchar(10) NOT NULL,
  `key_name` text NOT NULL,
  `value` text NOT NULL,
  `comment` varchar(50) DEFAULT NULL,
  `scene` varchar(512) DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `updated_by` varchar(128) DEFAULT NULL,
  `created_by` varchar(128) DEFAULT NULL,
  `is_delete` tinyint(1) DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `unique_md5` varchar(16) DEFAULT NULL COMMENT '唯一索引',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_md5_index` (`unique_md5`)
) ENGINE=InnoDB AUTO_INCREMENT=412544 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
ALTER TABLE i18n_config ADD unique_md5 varchar(16) DEFAULT NULL COMMENT '唯一索引';
create unique index unique_md5_index on i18n_config (unique_md5);
alter table i18n_config add status tinyint DEFAULT NULL COMMENT "新增待上架（status = 1）变更待同步 （status = 2）上架（status = 3）待下架（status = 4） 下架（status = 5）"
alter table i18n_config add scene varchar(512) DEFAULT NULL COMMENT "场景"
alter table i18n_config add `operator` tinyint(4) DEFAULT NULL COMMENT '创建（operator = 1）修改（operator = 2）删除（operator = 3）',

# i18n 描述表
CREATE TABLE `i18n_comment` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `cn_value` text NOT NULL,
  `comment` varchar(50) DEFAULT NULL,
  `en_unique_md5` varchar(16) DEFAULT NULL COMMENT '唯一索引',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` datetime DEFAULT NULL,
  `created_by` varchar(128) DEFAULT NULL,
  `updated_by` varchar(128) DEFAULT NULL,
  `is_delete` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

# 应用类型 与 语言未上架数据表 关联表
CREATE TABLE `i18n_classify_mapping_status_data` (
  `classify_id` bigint(20) unsigned NOT NULL,
  `en_unique_md5` varchar(16) DEFAULT NULL COMMENT '唯一索引',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_name` (`classify_id`,`en_unique_md5`)
) ENGINE=InnoDB AUTO_INCREMENT=376543 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
# i18n 语言未上架数据表
CREATE TABLE `i18n_config_status_data` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `type` varchar(10) NOT NULL,
  `key_name` text NOT NULL,
  `value` text NOT NULL,
  `comment` varchar(50) DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `updated_by` varchar(128) DEFAULT NULL,
  `created_by` varchar(128) DEFAULT NULL,
  `scene` varchar(512) DEFAULT NULL,
  `is_delete` tinyint(1) DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `unique_md5` varchar(16) DEFAULT NULL COMMENT '唯一索引',
  `status` tinyint(4) DEFAULT NULL COMMENT '新增待上架（status = 1）变更待同步 （status = 2）上架（status = 3）待下架（status = 4） 下架（status = 5）',
  `operator` tinyint(4) DEFAULT NULL COMMENT '创建（operator = 1）修改（operator = 2）删除（operator = 3）',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_md5_index` (`unique_md5`)
) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci'

# i18n 分类表
CREATE TABLE `i18n_classify` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50)  NOT NULL,
  `updated_by` varchar(128) DEFAULT NULL,
  `created_by` varchar(128) DEFAULT NULL,
  `is_delete` tinyint(1) DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `i18n_classify_uname` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO i18n_classify (name) values ('PC'); 
INSERT INTO i18n_classify (name) values ('H5'); 
INSERT INTO i18n_classify (name) values ('Android'); 
INSERT INTO i18n_classify (name) values ('IOS'); 
INSERT INTO i18n_classify (name) values ('ContentMarketing');

# i18n 分类和语言表mapping表
CREATE TABLE `i18n_classify_mapping` (
  `classify_id` bigint(20) unsigned NOT NULL,
  `i18n_id` bigint(20) unsigned NOT NULL
) ENGINE=InnoDB CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
ALTER TABLE i18n_classify_mapping ADD `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
ALTER TABLE i18n_classify_mapping ADD `created_at` datetime DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE i18n_classify_mapping ADD `id` bigint(20) unsigned NOT NULL;
ALTER TABLE i18n_classify_mapping ADD  PRIMARY KEY (id);
ALTER TABLE i18n_classify_mapping MODIFY id bigint(20) unsigned NOT NULL AUTO_INCREMENT;
ALTER TABLE i18n_classify_mapping ADD UNIQUE index_name(classify_id, i18n_id);
CREATE TABLE `i18n_classify_mapping` (
  `classify_id` bigint(20) unsigned NOT NULL,
  `i18n_id` bigint(20) unsigned NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_name` (`classify_id`,`i18n_id`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `data_options` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `parent_id` bigint(20) DEFAULT NULL,
  `type` varchar(128) NOT NULL COMMENT '一级分类',
  `value` varchar(512) NOT NULL,
  `class_type` varchar(128) DEFAULT NULL COMMENT '二级分类',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
alter table data_options auto_increment=10000;
insert into data_options (type, value, parent_id) values ('version', '1.0.0', 10000);
insert into data_options (type, value, parent_id) values ('version', '1.0.0', 10001);
insert into data_options (type, value, parent_id) values ('version', '1.0.0', 10002);
insert into data_options (type, value, parent_id) values ('version', '1.0.0', 10003);
insert into data_options (type, value, parent_id) values ('version', '1.0.0', 10004);

alter table yida_pages MODIFY page_data LONGTEXT not null

// insert into user (id, username, password, created_at) values (10000, 'ljy', '9abbc042f6b66cb88411', '2022-05-02 13:28:00');
const random = require('string-random');
const password = random(16, { specials: true });
const crypto = require('crypto');
const salt = 'xxx_config_center';
const getSaltPassword = password => {
    return crypto.createHash('md5').update(password + salt).digest('hex')
      .substr(0, 20);
  }
console.log(password);
console.log(getSaltPassword(password));
// 'XhY.yq8^(E:Pu{gY'
// '9abbc042f6b66cb88411'

sql 查询

let str = '';
let copyStr = '';
const params = [ type ];
if (keyName) {
  str = 'and A.key_name = ?';
  params.push(keyName);
}
params.push(type);
if (keyName) {
  copyStr = 'and key_name = ?';
  params.push(keyName);
}
const fields = [ 'id', 'type', 'key_name', 'value', 'comment', 'deleted_at', 'updated_by', 'created_by', 'is_delete', 'created_at', 'updated_at', 'unique_md5', 'status', 'en_unique_md5', 'scene', 'operator' ];
let fieldStr = '';
let fieldStrCopy = '';
fields.forEach((key, index) => {
  if (index === fields.length - 1) {
    fieldStr += `IFNULL(B.${key}, A.${key}) as ${key}`;
    fieldStrCopy += `${key}`;
    return;
  }
  fieldStr += `IFNULL(B.${key}, A.${key}) as ${key}, `;
  fieldStrCopy += `${key}, `;
});
const sql = `select ${fieldStr} from i18n_config A left join i18n_config_status_data B ON A.unique_md5 = B.unique_md5 where A.type = ? ${str}
UNION select ${fieldStrCopy} from i18n_config_status_data B where status = 1 and type = ? ${copyStr}
    `;