PostgreSQL与MySQL基本操作语法区别分析
原创
PostgreSQL与MySQL基本操作语法区别分析
原创
PostgreSQL与MySQL基本操作语法区别分析
在当今的数据库管理系统中，PostgreSQL和MySQL都是极为流行的开源数据库。尽管它们有许多相似之处，但在实际操作和语法上仍存在不少差异。
数据类型定义与区别
首先，让我们从数据类型开始。PostgreSQL和MySQL在数据类型定义上存在一些显著的差异。
1. 文本类型
在MySQL中，TEXT数据类型有一个最大存储容量限制，为64KB。然而，PostgreSQL中的TEXT数据类型则没有这样的限制。例如：
-- MySQL
CREATE TABLE example_mysql (
text_column TEXT
);
-- PostgreSQL
CREATE TABLE example_pg (
text_column TEXT
);2. 整数类型
MySQL使用TINYINT、MEDIUMINT和INT来表示不同大小的整数，而PostgreSQL则使用SMALLINT、INT和BIGINT。例如：
-- MySQL
CREATE TABLE example_mysql (
small_int_column TINYINT,
medium_int_column MEDIUMINT,
int_column INT
);
-- PostgreSQL
CREATE TABLE example_pg (
small_int_column SMALLINT,
int_column INT,
big_int_column BIGINT
);3. 字符串表示
在MySQL中，字符串可以用单引号或双引号表示，而PostgreSQL只接受单引号。PostgreSQL还使用E'…'来表示带有转义序列的字符串。例如：
-- MySQL
SELECT 'Hello, World!';
SELECT "Hello, World!";
-- PostgreSQL
SELECT 'Hello, World!';
SELECT E'Hello, \nWorld!';字符串与日期时间操作
1. 字符串连接
在MySQL中，可以使用+或CONCAT函数来连接字符串，而在PostgreSQL中，则使用||。例如：
-- MySQL
SELECT CONCAT('Hello, ', 'World!');
SELECT 'Hello, ' + 'World!';
-- PostgreSQL
SELECT 'Hello, ' || 'World!';2. 获取当前日期时间
MySQL使用NOW()来获取当前日期和时间，而PostgreSQL则使用CURRENT_TIMESTAMP或CURRENT_DATE。例如：
-- MySQL
SELECT NOW();
-- PostgreSQL
SELECT CURRENT_TIMESTAMP;
SELECT CURRENT_DATE;3. 日期时间的增减
MySQL使用DATE_ADD()和DATE_SUB()等函数来添加或减少日期时间，而PostgreSQL则使用INTERVAL。例如：
-- MySQL
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);
SELECT DATE_SUB(NOW(), INTERVAL 1 DAY);
-- PostgreSQL
SELECT NOW() + INTERVAL '1 day';
SELECT NOW() - INTERVAL '1 day';查询控制与结果集处理
1. LIMIT子句
在MySQL和PostgreSQL中，LIMIT子句都用于限制结果集中返回的记录数，但它们的语法略有不同。
- MySQL：
LIMIT offset, count，其中offset为起始位置，count为返回记录数。 - PostgreSQL：
LIMIT count OFFSET offset，其中count为返回记录数，offset为起始位置。
例如：
-- MySQL
SELECT * FROM example_mysql LIMIT 10, 5; -- 从第10条记录开始，返回5条记录
-- PostgreSQL
SELECT * FROM example_pg LIMIT 5 OFFSET 10; -- 从第10条记录开始，返回5条记录2. 检查NULL值
在MySQL中，可以使用= NULL或IS NULL来检查NULL值，使用IS NOT NULL来检查非NULL值。而在PostgreSQL中，只能使用IS NULL和IS NOT NULL。例如：
-- MySQL
SELECT * FROM example_mysql WHERE column_name = NULL; -- 错误
SELECT * FROM example_mysql WHERE column_name IS NULL; -- 正确
-- PostgreSQL
SELECT * FROM example_pg WHERE column_name IS NULL; -- 正确
SELECT * FROM example_pg WHERE column_name = NULL; -- 错误自增主键与变量声明
1. 自增主键
在MySQL中，可以使用AUTO_INCREMENT关键字将主键列设置为自增列。而在PostgreSQL中，则使用SERIAL或BIGSERIAL类型来创建自增主键列。例如：
-- MySQL
CREATE TABLE example_mysql (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100)
);
-- PostgreSQL
CREATE TABLE example_pg (
id SERIAL PRIMARY KEY,
name VARCHAR(100)
);2. 变量和参数
MySQL使用@符号来声明和使用用户变量，使用?作为占位符来传递参数。而PostgreSQL则使用冒号(:)来声明和使用变量，使用$1、$2等占位符来传递参数。例如：
-- MySQL
SET @var = 1;
SELECT * FROM example_mysql WHERE id = @var;
-- PostgreSQL
DO $$
DECLARE var INTEGER := 1;
BEGIN
SELECT * FROM example_pg WHERE id = var;
END $$;
-- 使用占位符传递参数（在存储过程或预处理语句中）
PREPARE stmt FROM 'SELECT * FROM example_pg WHERE id = $1';
EXECUTE stmt USING 1;连接与存储过程
1. 连接类型
在MySQL和PostgreSQL中，都可以使用JOIN、LEFT JOIN、RIGHT JOIN和INNER JOIN来进行不同类型的连接。虽然语法相似，但在实际应用中可能有一些细微的差别。例如：
-- MySQL
SELECT a.*, b.*
FROM table_a a
INNER JOIN table_b b ON a.id = b.a_id;
-- PostgreSQL
SELECT a.*, b.*
FROM table_a a
INNER JOIN table_b b ON a.id = b.a_id;2. 存储过程与函数
在PostgreSQL中，存储过程是通过定义存储过程函数来创建的，而在MySQL中，存储过程是通过在脚本文件（.sql）中定义来创建的。此外，MySQL还支持触发器（TRIGGER），而PostgreSQL的触发器和存储过程语法略有不同。
例如，在MySQL中创建一个简单的存储过程：
-- MySQL
DELIMITER //
CREATE PROCEDURE SimpleProcedure()
BEGIN
SELECT 'Hello from MySQL!';
END //
DELIMITER ;
CALL SimpleProcedure();在PostgreSQL中创建一个简单的存储过程：
-- PostgreSQL
CREATE OR REPLACE FUNCTION SimpleFunction()
RETURNS TEXT AS $$
BEGIN
RETURN 'Hello from PostgreSQL!';
END;
$$ LANGUAGE plpgsql;
SELECT SimpleFunction();。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
腾讯云开发者
