TeleDB 和 TelePG的区别 （MySQL—PgSQL）
·
TeleDB 和 TelePG的区别 （MySQL—PgSQL）
-
数据类型差异：
整型
MySQL中的TEXT数据类型最大存储容量为64KB，而PostgreSQL中的TEXT数据类型没有此限制。 MySQL中使用TINYINT、MEDIUMINT和INT表示不同大小的整数，而PostgreSQL使用SMALLINT、INT和BIGINT。浮点数类型
MySQL 提供了 FLOAT 和 DOUBLE 类型来表示浮点数，可以指定精度。 PostgreSQL 提供了 REAL 和 DOUBLE PRECISION 类型来表示浮点数。 -
字符串引号：
MySQL中可以使用''或""来表示字符串，而PostgreSQL只接受''来表示字符串。 PostgreSQL使用E'…' 来表示带有转义序列的字符串。 双引号在 PostgreSQL 中用于引用标识符（如表名、列名等），而不是字符串值。 MySQL 使用 VARCHAR 来表示可变长度的字符串，例如 VARCHAR(255)， VARCHAR(n)最大只能是64kb。而postgresql中的VARCHAR(n) 最大可以存 1GB。 PostgreSQL 使用 VARCHAR 或 TEXT 来表示可变长度的字符串，postgresql中的TEXT相当于MySQL中的longtext。
MySQL中可以使用单引号或双引号来表示字符串，而PostgreSQL只接受单引号来表示字符串。
SELECT 'Hello, World!' AS message;
SELECT "Hello, World!" AS message;
PostgreSQL使用E’…’ 来表示带有转义序列的字符串。
使用单引号表示字符串:
SELECT 'Hello, World!' AS message;
使用E'…' 表示带有转义序列的字符串:
SELECT E'This is a backslash: \\' AS message;
MySQL 不直接支持数组类型，但可以使用逗号分隔的字符串来模拟数组。
PostgreSQL 支持原生的数组类型，如 integer[]、varchar[] 等。
MySQL
使用"+"连接字符串：
SELECT 'Hello, ' + 'World!' AS message;
使用CONCAT函数连接字符串：
SELECT CONCAT('Hello, ', 'World!') AS message;
PostgreSQL使用"||"连接字符串：
SELECT 'Hello, ' || 'World!' AS message;
-
日期和时间函数差异：
MySQL使用NOW()来获取当前日期时间，而PostgreSQL使用CURRENT_TIMESTAMP或CURRENT_DATE来获取。
MySQL使用DATE_ADD()、DATE_SUB()等函数来添加或减少日期时间，而PostgreSQL使用INTERVAL进行类似操作。MySQL 获取当前日期时间： select CURDATE() 2024-02-27 select CURTIME() 17:49:46 SELECT NOW() AS current_datetime; SELECT CURRENT_TIMESTAMP AS current_datetime; 添加或减少日期时间： SELECT DATE_ADD('2023-01-01', INTERVAL 1 DAY) AS date_plus_one_day; SELECT DATE_SUB('2023-01-01', INTERVAL 1 MONTH) AS date_minus_one_month; PgSQL 获取当前日期时间： SELECT NOW() AS current_datetime; SELECT CURRENT_TIMESTAMP AS current_datetime; 添加或减少日期时间： SELECT '2023-01-01'::date + INTERVAL '1 day' AS date_plus_one_day; SELECT '2023-01-01'::date - INTERVAL '1 month' AS date_minus_one_month; 1.MySQL将字符串转换为日期： SELECT STR_TO_DATE('2023-07-19', '%Y-%m-%d') AS converted_date; 2.MySQL将日期转换为字符串： SELECT DATE_FORMAT(NOW(), '%Y-%m-%d') AS converted_string; 1.PGSQL将字符串转换为日期： SELECT TO_DATE('2023-07-19', 'YYYY-MM-DD') AS converted_date; 2.PGSQL将日期转换为字符串： SELECT TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD') AS converted_string; -
LIMIT子句差异：
在MySQL中，LIMIT子句用于限制结果集中返回的记录数，语法为"LIMIT offset, count"，offset为起始位置，count为返回记录数。
-- 返回结果集的前5条记录 SELECT * FROM table_name LIMIT 5; -- 返回从第11条开始的5条记录 SELECT * FROM table_name LIMIT 10, 5; SELECT * FROM table_name LIMIT 5 OFFSET 10;在PostgreSQL中，LIMIT子句用于限制结果集中返回的记录数，语法为"LIMIT count OFFSET offset"，count为返回记录数，offset为起始位置。
-- 返回结果集的前5条记录 SELECT * FROM table_name LIMIT 5; -- 返回从第11条开始的5条记录 SELECT * FROM table_name LIMIT 5 OFFSET 10; -
NULL值处理：
在MySQL中，使用"= NULL"或"IS NULL"来检查NULL值，使用"IS NOT NULL"来检查非NULL值。
-- 检查字段是否为NULL SELECT * FROM table_name WHERE column_name IS NULL; -- 检查字段是否不为NULL SELECT * FROM table_name WHERE column_name IS NOT NULL;在PostgreSQL中，使用"IS NULL"来检查NULL值，使用"IS NOT NULL"来检查非NULL值。
-- 检查字段是否为NULL SELECT * FROM table_name WHERE column_name IS NULL; -- 检查字段是否不为NULL SELECT * FROM table_name WHERE column_name IS NOT NULL; -
自增主键列：
在MySQL中，可以使用AUTO_INCREMENT关键字将主键列设置为自增列。
在PostgreSQL中，可以使用SERIAL或BIGSERIAL类型来创建自增主键列。-- 使用 SERIAL 类型创建自增主键列 CREATE TABLE users ( id SERIAL PRIMARY KEY, username VARCHAR(50), email VARCHAR(100) ); -- 使用 BIGSERIAL 类型创建自增主键列 CREATE TABLE products ( id BIGSERIAL PRIMARY KEY, name VARCHAR(100), price DECIMAL(10,2) ); -
变量和参数差异：
MySQL使用@符号来声明和使用用户变量，使用？来作为占位符来传递参数。
PostgreSQL使用冒号（：）来声明和使用变量，使用 $1、$2 等占位符来传递参数。-- 声明一个名为my_var的用户变量，并将其赋值为'Hello World' SET @my_var := 'Hello World'; -- 打印变量的值 SELECT @my_var; ---------------------------------------------- -- 查询指定用户的信息，使用占位符传递参数 SELECT username, email FROM users WHERE id = ?; -
连接查询：
在MySQL中，可以使用JOIN、LEFT JOIN、RIGHT JOIN和INNER JOIN来进行不同类型的连接。
（1） INNER JOIN (或简写为 JOIN): 返回两个表中都有的记录。
SELECT A.name, B.age FROM tableA A INNER JOIN tableB B ON A.id = B.id;
（2） LEFT JOIN (或简写为 LEFT OUTER JOIN): 返回左表的所有记录，以及右表中匹配的记录。如果在右表中没有匹配的记录，则结果为NULL。
SELECT A.name, B.age FROM tableA A LEFT JOIN tableB B ON A.id = B.id;
（3）RIGHT JOIN (或简写为 RIGHT OUTER JOIN): 返回右表的所有记录，以及左表中匹配的记录。如果在左表中没有匹配的记录，则结果为NULL。
SELECT A.name, B.age
FROM tableA A RIGHT JOIN tableB B ON A.id = B.id;
在PostgreSQL中，使用JOIN来进行内连接，使用LEFT JOIN和RIGHT JOIN来进行左连接和右连接。
PostgreSQL 的语法与MySQL类似，但JOIN默认就是INNER JOIN，其他JOIN操作需要明确指定。
（1）INNER JOIN (或简写为 JOIN): 与MySQL中的INNER JOIN相同。
SELECT A.name, B.age
FROM tableA A JOIN tableB B ON A.id = B.id;
（2）LEFT JOIN (或简写为 LEFT OUTER JOIN): 与MySQL中的LEFT JOIN相同。
SELECT A.name, B.age
FROM tableA A LEFT JOIN tableB B ON A.id = B.id;
（3）RIGHT JOIN (或简写为 RIGHT OUTER JOIN): 与MySQL中的RIGHT JOIN相同。
SELECT A.name, B.age
FROM tableA A RIGHT JOIN tableB B ON A.id = B.id;
字符串比较：在 PostgreSQL 中，字符串比较是区分大小写的，默认情况下使用区分大小写的排序规则。而在 MySQL 中，字符串比较默认是不区分大小写的，可以使用 COLLATE 子句来指定不同的排序规则。
聚合函数的引用：在 PostgreSQL 中，HAVING 子句可以直接引用 SELECT 语句中定义的聚合函数，如 HAVING SUM(column) > 100。而在 MySQL 中，默认情况下不允许在 HAVING 子句中直接使用 SELECT 语句中定义的聚合函数结果，需要使用重复的聚合函数表达式，如 HAVING SUM(column) > 100 和 HAVING SUM(column) > 200。
NULL 值处理：在 PostgreSQL 中，HAVING 子句可以直接使用 NULL 进行比较，如 HAVING column IS NULL。而在 MySQL 中，需要使用特殊函数 IS NULL 或 IS NOT NULL 进行 NULL 值的比较，如 HAVING IS NULL(column)。
在 PostgreSQL 中，不能直接在 HAVING 子句中使用 SELECT 语句中定义的列别名进行引用。而在 MySQL 中，默认情况下也不支持在 HAVING 子句中直接使用 SELECT 语句中的列别名。然而，从 MySQL 5.7+ 版本开始，已经开始支持使用列别名。
在选择列表中的非聚合列：
MySQL 允许在 GROUP BY 子句中不列出选择列表中的非聚合列，而且结果集将使用该组中的任意一个值。这可能会导致不可预测的结果。
PostgreSQL 要求选择列表中的非聚合列要么包含在 GROUP BY 子句中，要么在聚合函数中使用。否则，会引发错误。
聚合函数的处理:
MySQL 允许在 SELECT 语句中使用聚合函数，即使没有使用 GROUP BY 子句。这将导致对整个结果集进行聚合计算并返回单个结果。
PostgreSQL 要求在使用聚合函数时必须使用 GROUP BY 子句，并按照指定的分组进行计算。
在MySQL中
SELECT name, AVG(score) AS avg_score
FROM student
GROUP BY name;
在postgreSQL中
SELECT name, AVG(score) AS avg_score
FROM student
GROUP BY name;
--- 报错！！！
mysql和pg中都支持cast(expression as target_type)的方法去进行类型转换，但是pg中除此之外还支持value::new_type的方法来进行类型转换。
select cast(id as char) from t1 limit 1;
select id::text from t1 limit 1;
pg中的upsert作用是当插入数据时：如果不存在则insert，存在则update。
语法为：
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
[ ON CONFLICT [ conflict_target ] conflict_action ]
and conflict_action is one of:
DO NOTHING
DO UPDATE SET { column_name = { expression | DEFAULT } |
( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
( column_name [, ...] ) = ( sub-SELECT )
} [, ...]
[ WHERE condition ]
举例：
-- 创建表
create table test(id int primary key, info text, crt_time timestamp);
-- 不存在则插入，存在则更新
insert into test values (1,'test',now())
on conflict (id)
do update set info=excluded.info,crt_time=excluded.crt_time;
-- 不存在则插入，存在则不处理
insert into test values (1,'hello digoal',now()) on conflict (id) do nothing;
mysql中使用replace来实现类似的功能。
语法为：
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[(col_name [, col_name] ...)]
{ {VALUES | VALUE} (value_list) [, (value_list)] ...
|
VALUES row_constructor_list
}
例子：
用于替换或插入一行数据。如果表中已经存在具有指定主键或唯一索引的行，则该行将被替换；如果不存在，则将插入新行。
REPLACE INTO test VALUES (1, 'Old', '2014-08-20 18:47:00');
SELECT * FROM test;
+----+------+---------------------+
| id | data | ts |
+----+------+---------------------+
| 1 | Old | 2014-08-20 18:47:00 |
+----+------+---------------------+
REPLACE INTO test VALUES (1, 'New', '2014-08-20 18:47:42');
SELECT * FROM test;
+----+------+---------------------+
| id | data | ts |
+----+------+---------------------+
| 1 | New | 2014-08-20 18:47:42 |
+----+------+---------------------+
四、函数区别：
(1)时间转化函数
①时间转字符串：
MySQL: date_format(a.tag_create_date,'%Y-%m-%d %H:%i:%s')
PostgreSQL: to_char(a.tag_create_date,'yyyy-mm-dd HH:MM:SS')
②字符串转时间：
MySQL: date_format(a.tag_create_date,'%Y-%m-%d %H:%i:%s')
PostgreSQL: to_date(a.tag_create_date,'yyyy-mm-dd HH:MM:SS')
(2) IFNULL()函数
MySQL: IFNULL(a.idm,'') select ifnull(avatar, 'aa'),"name" from t_user;
PostgreSQL: COALESCE(a.id,'') select COALESCE(avatar, 'aa'),"name" from t_user;
(3) sysdate()函数
MySQL: SELECT sysdate()
PostgreSQL: SELECT now()
(4) find_in_set()函数（允许在逗号分隔的字符串列表中查找指定字符串的位置）
MySQL: SELECT t.dept_id FROM sys_dept t WHERE find_in_set('100', ancestors)
PostgreSQL: SELECT t.dept_id FROM sys_dept t WHERE '100' = ANY (string_to_array(ancestors, ','))
(5)group_concat()函数
MySQL: select a.name,group_concat(distinct city)from user_city a group by a.name;
PostgreSQL: select a.name,array_to_string(array_agg(distinct a.city),',')from user_city a group by a.name;
（6）DISTINCT
MYSQL: select DISTINCT b.id from hospital.ods_user_basic as b
PostgreSQL: select DISTINCT ON (b.id) b.* from hospital.ods_user_basic as b
(注：pg中祛重+排序 ，使用distinct on (列1) order by 列1 )
（7）MyBatis-Plus整合MySQL、PostgreSQL，LIKE使用
MySQL：
SELECT *
FROM infisa_medical_report
WHERE is_deleted=0 AND status = 1 AND name LIKE concat('%',#{handleTask.name},'%')
PostgreSQL:
SELECT *
FROM infisa_medical_report
WHERE is_deleted=0 AND status = 1 AND name like concat('%',#{handleTask.name}::varchar,'%')
（注：pg必须指定模糊查询列的数据类型，否则会报错）
汇聚全球AI编程工具，助力开发者即刻编程。
更多推荐
3
0- 0
已为社区贡献1条内容
所有评论(0)