MySQL与Oracle 差异比较

JoyceYoungYYY

已于 2022-04-22 14:01:33 修改

阅读量560

点赞数

CC 4.0 BY-SA版权

分类专栏：数据库 oracle mysql 文章标签： mysql oracle

于 2022-02-23 20:24:10 首次发布

本文链接：https://blog.csdn.net/qq_37931744/article/details/122878711

mysql 同时被 3 个专栏收录

11 篇文章

订阅专栏

oracle

4 篇文章

订阅专栏

数据库

1 篇文章

订阅专栏

本文对比了MySQL与Oracle在日期格式化和列表聚合方面的差异。在Oracle中，使用to_char进行日期格式化，而在MySQL中则使用DATE_FORMAT。Oracle的listagg功能在MySQL中可以通过group_concat实现。同时，展示了两个数据库的建表和数据插入示例，以及相应的查询语句来聚合标签数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MySQL与Oracle 差异比较之三函数

一、日期格式化带中文

# oracle
SELECT to_char(sysdate,'yyyy"年"mm"月"dd"日"') FROM DUAL;
# mysql
select DATE_FORMAT(now(), '%Y年%m月%d日')；

二、oracle中的listagg与mysql对应实现

参考地址

1、oracle建表

create table TRN_TAGS
(
    ID               VARCHAR2(32) not null  primary key,
    TAGS_TXT         VARCHAR2(255)
);


comment on table TRN_TAGS is '标签表';
comment on column TRN_TAGS.TAGS_TXT is '标签内容';

create table TRN_TEACHER_TAGS
(
    ID               VARCHAR2(32) not null primary key,
    TLL_TEA_ID       VARCHAR2(32),
    TLL_TAGS_ID      VARCHAR2(2000)
);


comment on table TRN_TEACHER_TAGS is '讲师个性标签表';
comment on column TRN_TEACHER_TAGS.ID is '主键id';
comment on column TRN_TEACHER_TAGS.TLL_TEA_ID is '讲师ID';
comment on column TRN_TEACHER_TAGS.TLL_TAGS_ID is '标签ID';

2、mysql建表

CREATE TABLE `TRN_TEACHER_TAGS` (
  `ID` varchar(32) NOT NULL COMMENT '主键id',
  `TLL_TEA_ID` varchar(32) DEFAULT NULL COMMENT '讲师ID',
  `TLL_TAGS_ID` varchar(2000) DEFAULT NULL COMMENT '标签ID',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='讲师个性标签表';

CREATE TABLE `TRN_TAGS` (
  `ID` varchar(32) NOT NULL COMMENT '主键id',
  `TAGS_TXT` varchar(255) DEFAULT NULL COMMENT '标签内容',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='标签表';

3、造数据

INSERT INTO TRN_TEACHER_TAGS (ID, TLL_TEA_ID, TLL_TAGS_ID) VALUES ('111', '111', '111');
INSERT INTO TRN_TEACHER_TAGS (ID, TLL_TEA_ID, TLL_TAGS_ID) VALUES ('222', '111', '222');
INSERT INTO TRN_TEACHER_TAGS (ID, TLL_TEA_ID, TLL_TAGS_ID) VALUES ('333', '111', '333');
INSERT INTO TRN_TEACHER_TAGS (ID, TLL_TEA_ID, TLL_TAGS_ID) VALUES ('444', '111', '444');
INSERT INTO TRN_TEACHER_TAGS (ID, TLL_TEA_ID, TLL_TAGS_ID) VALUES ('9898', '111', '555');
INSERT INTO TRN_TEACHER_TAGS (ID, TLL_TEA_ID, TLL_TAGS_ID) VALUES ('898', '111', '666');
INSERT INTO TRN_TEACHER_TAGS (ID, TLL_TEA_ID, TLL_TAGS_ID) VALUES ('89', '111', '777');
INSERT INTO TRN_TEACHER_TAGS (ID, TLL_TEA_ID, TLL_TAGS_ID) VALUES ('89777', '111', '888');
INSERT INTO TRN_TEACHER_TAGS (ID, TLL_TEA_ID, TLL_TAGS_ID) VALUES ('8777', '111', '999');
INSERT INTO TRN_TEACHER_TAGS (ID, TLL_TEA_ID, TLL_TAGS_ID) VALUES ('556', '111', '100');
INSERT INTO TRN_TEACHER_TAGS (ID, TLL_TEA_ID, TLL_TAGS_ID) VALUES ('6', '111', '0000');
INSERT INTO TRN_TEACHER_TAGS (ID, TLL_TEA_ID, TLL_TAGS_ID) VALUES ('11y1', '22', '111');
INSERT INTO TRN_TEACHER_TAGS (ID, TLL_TEA_ID, TLL_TAGS_ID) VALUES ('22u2', '22', '222');
INSERT INTO TRN_TEACHER_TAGS (ID, TLL_TEA_ID, TLL_TAGS_ID) VALUES ('33t3', '22', '333');
INSERT INTO TRN_TEACHER_TAGS (ID, TLL_TEA_ID, TLL_TAGS_ID) VALUES ('4yu44', '22', '444');
INSERT INTO TRN_TEACHER_TAGS (ID, TLL_TEA_ID, TLL_TAGS_ID) VALUES ('u9898', '22', '555');
INSERT INTO TRN_TEACHER_TAGS (ID, TLL_TEA_ID, TLL_TAGS_ID) VALUES ('8k98', '22', '666');
INSERT INTO TRN_TEACHER_TAGS (ID, TLL_TEA_ID, TLL_TAGS_ID) VALUES ('89y', '22', '777');
INSERT INTO TRN_TEACHER_TAGS (ID, TLL_TEA_ID, TLL_TAGS_ID) VALUES ('8u9777', '22', '888');
INSERT INTO TRN_TEACHER_TAGS (ID, TLL_TEA_ID, TLL_TAGS_ID) VALUES ('8u777', '22', '999');
INSERT INTO TRN_TEACHER_TAGS (ID, TLL_TEA_ID, TLL_TAGS_ID) VALUES ('6778j', '22', '0000');

INSERT INTO TRN_TAGS (ID,TAGS_TXT) VALUES ('111',  'zbc');
INSERT INTO TRN_TAGS (ID,TAGS_TXT) VALUES ('222',  'zxc');
INSERT INTO TRN_TAGS (ID,TAGS_TXT) VALUES ('333',  'zcc');
INSERT INTO TRN_TAGS (ID,TAGS_TXT) VALUES ('444',  '555');
INSERT INTO TRN_TAGS (ID,TAGS_TXT) VALUES ('555',  '888');
INSERT INTO TRN_TAGS (ID,TAGS_TXT) VALUES ('666',  '877');
INSERT INTO TRN_TAGS (ID,TAGS_TXT) VALUES ('777',  '866');
INSERT INTO TRN_TAGS (ID,TAGS_TXT) VALUES ('888',  '567');
INSERT INTO TRN_TAGS (ID,TAGS_TXT) VALUES ('999',  '890');
INSERT INTO TRN_TAGS (ID,TAGS_TXT) VALUES ('100',  '677');
INSERT INTO TRN_TAGS (ID,TAGS_TXT) VALUES ('0000',  '556');

4、实现方式

oracle

select listagg(a.TAGS_TXT,',') WITHIN GROUP ( ORDER BY a.TLL_TEA_ID) as tags from (
    select tt.TLL_TEA_ID,t.TAGS_TXT
	from TRN_TAGS t
	left join TRN_TEACHER_TAGS tt on tt.TLL_TAGS_ID=t.id) a
group by a.TLL_TEA_ID;

在这里插入图片描述

mysql

select a.TLL_TEA_ID ,group_concat(a.TAGS_TXT separator ',') from (
	select tt.TLL_TEA_ID,t.TAGS_TXT
	from TRN_TAGS t 
	left join TRN_TEACHER_TAGS tt on tt.TLL_TAGS_ID=t.id
) a group by a.TLL_TEA_ID;

在这里插入图片描述