# Mysql
create databse short_url_db DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
create user 'short_url_user'@'%' identified by 'welcome';
grant all on short_url_db.* to 'short_url_user'@'%';
2. 以一个已经存在的表作为模板, 创建一个新表
# Mysql
create table <table_name_new> like <table_name_old>
# Postgres
create table <table_name_new> (like <table_name_old> including all)
3. 修改一个库的字符编码和字符集
# Mysql
##1.先查询库(schema)的编码和字符集
ELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = <some_database> ;
##2.进行修改
ALTER DATABASE <some_database> character set utf8mb4 collate utf8mb4_unicode_ci;
# Postgres
4. 修改一张表的字符编码和字符集
# Mysql
alter table <some_table> convert to character set utf8mb4 collate utf8mb4_unicode_ci;
# Postgres
DML
1. 批量修改某张表里面的一些数据
# 将某一个字段内容的前缀进行统一修改
UPDATE <table_name> tanm
INNER JOIN (
SELECT
NO_MODE_ID,
CASE
WHEN GEN_PATTERN LIKE 'f:T%' THEN
CONCAT(
'f:D',
substring(GEN_PATTERN, 4)
)
ELSE
CONCAT(
'f:D',
substring(GEN_PATTERN, 3)
)
END AS GEN_PATTERN
FROM
<table_name>
) v ON tanm.NO_MODE_ID= v.NO_MODE_ID
SET tanm.GEN_PATTERN = v.GEN_PATTERN
DROP PROCEDURE IF EXISTS resetSort;
CREATE PROCEDURE resetSort()
BEGIN
DECLARE _id bigint default 0;
DECLARE i int default 0;
DECLARE cnt bigint default 0;
DECLARE ids cursor for SELECT id FROM health_product_catalog WHERE is_delete = 0 ORDER BY ctl_sort ASC;
SELECT COUNT(1) INTO cnt FROM health_product_catalog WHERE is_delete = 0;
OPEN IDS;
WHILE i<=cnt DO
FETCH ids INTO _id;
SET i = i + 1;
UPDATE health_product_catalog SET ctl_sort = i WHERE id = _id;
END WHILE;
CLOSE IDS;
END;
CALL resetSort();
业务技巧
1. 列表排序的前移, 后移, 置顶, 置底;
UPDATE health_product_catalog x,
health_product_catalog y,
(
SELECT
a.id, ctl_sort
FROM
health_product_catalog a
WHERE
a.is_delete = 0
and ctl_parent_id = #{ctlParentId, jdbcType=VARCHAR}
<choose>
<when test="move=='top'">
ORDER BY a.ctl_sort ASC
</when>
<when test="move=='up'">
<![CDATA[ and a.ctl_sort < #{currentOrder, jdbcType=INTEGER} ]]>
ORDER BY a.ctl_sort DESC
</when>
<when test="move=='down'">
<![CDATA[ and a.ctl_sort > #{currentOrder, jdbcType=INTEGER} ]]>
ORDER BY a.ctl_sort ASC
</when>
<when test="move=='bottom'">
ORDER BY a.ctl_sort DESC
</when>
</choose>
LIMIT 1
) v
SET x.ctl_sort = v.ctl_sort,
y.ctl_sort = #{currentOrder, jdbcType=INTEGER}
WHERE x.id = #{id,jdbcType=BIGINT}
AND y.id = v.id