数据库的使用06：Mysql & Doris常见命令及其问题归纳

查看表结构（字段字段类型是否为空）

Susu2001

2597人浏览 · 2024-12-02 18:19:29

Susu2001 · 2024-12-02 18:19:29 发布

一、Doris的三大数据模型

（如何处理插入时主键重复的情况）

【备注】第三个模型Duplicate不允许更新，仅支持部分数据删除功能，推荐还是用第二种。这三大模型最大区别在于对insert语句的处理中，sqlserver插入重复主键就报错，Doris的Unique插入重复主键则更新，Doris的Duplicate插入重复主键则继续插入（允许主键重复）。

数据模型 - Apache Doris

二、常见命令

1.表

（1）查询表结构（字段字段类型是否为空）

DESCRIBE table_name;

DESC table_name;

上述命令：查表是准确的，查视图是不准确的（有些字段表可空，查视图居然就是不可空的）

（2）查看建表语法

SHOW CREATE TABLE table_name;

（3）结构及数据迁移

1.结构+数据

CREATE TABLE 新表 AS SELECT * FROM 旧表;

2.仅数据

INSERT INTO 新表 SELECT * FROM 旧表;

3.仅结构

CREATE TABLE 新表 LIKE 旧表;

（4）改表名

ALTER TABLE 旧表 RENAME 新表;

（5）查容量

SHOW DATA --查询数据库容量

（6）事务

BEGIN;
--编写你的SQL
ROLLBACK;--回滚（二选一）
COMMIT;--提交 （二选一）

（7）加表注释

ALTER TABLE 表名 MODIFY COMMENT  '这是一个对该表的注释信息';

（8）获取当前数据库名称

SELECT DATABASE();

（9）布隆过滤器

-- BloomFilter 索引能够对等值查询（包括 = 和 IN）加速，对高基数字段（重复少字段）效果较好，比如 userid 等唯一 ID 字段。
-- 索引的修改需要1-5min，可以过段时间在查询修改结果
-- 查询索引
SHOW CREATE TABLE table_name;
-- 增加/修改索引
ALTER TABLE table_name SET ("bloom_filter_columns" = "column_name1,column_name2,column_name3");
-- 删除索引
ALTER TABLE table_name SET ("bloom_filter_columns" = "");
-- 查看索引修改任务
SHOW ALTER TABLE COLUMN;

【所有表名称+表容量+表字段数量+表字段】

SELECT 
  t.TABLE_NAME AS TableName,
	s.TABLE_COMMENT AS TableDescription,  -- 获取表的注释
  MAX(t.TABLE_ROWS) AS RowCounts,  
  COUNT(c.COLUMN_NAME) AS FieldCount,  -- 计算字段的数量
  GROUP_CONCAT(c.COLUMN_NAME ORDER BY c.ORDINAL_POSITION) AS Fields
  
FROM 
  information_schema.tables t
JOIN 
  information_schema.columns c ON t.TABLE_NAME = c.TABLE_NAME AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
LEFT JOIN
  information_schema.tables s ON t.TABLE_NAME = s.TABLE_NAME AND t.TABLE_SCHEMA = s.TABLE_SCHEMA
WHERE 
  t.TABLE_SCHEMA = 'DbName'  -- 替换为你要查询的数据库名
  AND t.TABLE_TYPE = 'BASE TABLE'  -- 仅查询普通表，排除视图
GROUP BY 
  t.TABLE_NAME, s.TABLE_COMMENT
ORDER BY 
  t.TABLE_NAME;

2.字段

（1）修改字段类型

1.INT类型外

ALTER TABLE table_name 
MODIFY COLUMN 字段名称 
VARCHAR(100) NULL  -- 改为string100 可空
COMMENT "你的注释"

2.INT类型：不支持修改类型和名称，如有需要，删表重建：

⚪查建表语法

SHOW CREATE TABLE 你的表名

⚪复制查出来的建表SQL语法，并且将主键改为：

 `id` bigint NOT NULL  AUTO_INCREMENT(1),

⚪建表，然后转移数据，改好新旧表名即可

（2）新增字段

ALTER TABLE tablename
ADD COLUMN 新的字段 INT NOT NULL DEFAULT "0" 
COMMENT "你的注释";

（3）删除字段

ALTER TABLE tablename
DROP COLUMN 不想要的字段;

（4）主键改为联合主键

如果下面方案不行，drop掉再建：

-- 删除原来的主键
ALTER TABLE users DROP PRIMARY KEY;

-- 添加新的联合主键 (id, name, age)
ALTER TABLE users ADD PRIMARY KEY (id, name, age);

drop掉再建，建表语法设置：

（5）看注释

show full columns from TableName -- 查注释

（6）默认时间

建表时候可以给【创建时间】字段加上默认值：

  `CreateTime` datetime(3) NULL default current_timestamp,

（7）加索引

ALTER TABLE 表名 SET ("bloom_filter_columns" = "字段1,字段2");

3.视图

（1）创建

CREATE VIEW 视图名称 AS  你的SQL ; -- 创建视图的语法

注意很坑的点：

create view as 视图名称 AS
select a.id,b.name 
from student a
inner join details b on a.id = b.id

假设b表没有name这个字段，报错可能会报"b表没有age字段（其他字段）"，逐个字段注释排查看看

常见的错误：

视图名称和表名称一致
逗号用中文
某表没有这个字段
两表字段重复了但是没有起别名

三、查表、视图、字段

--获取所有视图
SELECT TABLE_NAME
FROM information_schema.views
WHERE TABLE_SCHEMA = '数据库名称' ORDER BY TABLE_NAME;

--获取所有表
SELECT TABLE_NAME
FROM information_schema.tables
WHERE TABLE_SCHEMA = '数据库名称' ORDER BY TABLE_NAME;

--获取所有表和视图
(SELECT TABLE_NAME FROM information_schema.views WHERE TABLE_SCHEMA = '数据库名称' ORDER BY TABLE_NAME)
UNION ALL
(SELECT TABLE_NAME FROM information_schema.tables WHERE TABLE_SCHEMA = '数据库名称' AND TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME)
ORDER BY TABLE_NAME
;

--获取所有字段名称
SELECT COLUMN_NAME
FROM information_schema.columns
WHERE TABLE_NAME = '你的表名';

四、结合Freesql的一些坑

1.查表

（1）整型转化问题cast sign

Where、ToList中不要用Convert.ToInt32(),如有需要，拆分来写：

string input_str = "1";
int input_int = string.IsNullOrEmpty(input_str) ? 0 : Convert.ToInt32(input_str);
var list = fsql.Select<User>()
            .WhereIf(input_int!=0,x=>x.Type == input_int)
            .ToList();

原因：freesql生成的mysql语法是 cast(a as signed)，然而doris仅支持cast(a as int)，然而后面这种是sqlserver的语法。

（2）ID查询一次不允许超过10000条

报错：Exceeded the maximum number of child expressions (10000)

问题如下：大多数据库都不支持一次in超过1000条，如果是in+or不支持超过10000条，可以程序处理！也可以修改expr_children_limit配置（见后面）。

//当查询的ID数量大于一万时，Doris会报错：Exceeded the maximum number of child expressions (10000)-超过了子表达式的最大数量（10000）
var ids = Enumerable.Range(0, 50000).ToList();
var test = Myfsql.Select<Student>().Where(x => ids.Contains(x.Id)).ToList();

解决方案：

查询配置

SHOW FRONTEND CONFIG;-- 其中IsMutable=true是可以动态修改的配置

SHOW FRONTEND CONFIG like '%expr_children_limit%'; -- 精确查找

更改配置

-- 例如我们要更改expr_children_limit的值为50000
ADMIN SET ALL FRONTENDS CONFIG ("expr_children_limit" = "50000");

（3）保留字报错问题

报错信息:

Encountered: LIKE
Expected: LIKE is keyword, maybe `LIKE`

改正:

select * from Config where Key Like '%123%' -- 报错，Key是Mysql保留字

select * from Config where `Key` Like '%123%' -- 加上``即可

2.插入和更新

当你插入的数据存在(联合)主键重复时，执行SQL会返回“影响一条，插入成功”，实际上并没有成功插入，默认是根据主键将新插入的替换旧的（即更新操作）。
不支持fsql的InsertOrUpdate方法（即不支持Mysql的DUPLICATE KEY UPDATE重复键更新语法）
不支持fsql的事务写法
设Null报错：用FreeSql.Provider.MySqlConnector包，不要用FreeSql.Provider.MySql包（如果一定要用，插入的时候可以IgnoreColumns那些设null的字段）

3.视图

如果建立视图时，SELECT了没有的字段，一律会报没有某字段的错，可以改为* 或者逐行注释字段排查出问题字段

五、配置

1.缓存配置开启

-- 开启缓存,设置所有查询累计的最大缓存空间
SET GLOBAL enable_sql_cache = true;
SET GLOBAL enable_partition_cache = true;
SET GLOBAL query_cache_size = 500 * 1024 * 1024;
SET GLOBAL query_cache_type = 1;
-- 查询缓存开启情况
SHOW VARIABLES LIKE '%cache%';

2.FE&BE配置

FE：管理集群元数据，负责查询协调与用户连接。
BE：存储实际数据，执行数据扫描与计算任务。

-- 语法设置：where in里面支持50000条
ADMIN SET ALL FRONTENDS CONFIG ("expr_children_limit" = "50000");

-- 缓存机制：最多缓存100个查询的数据,超过则依次释放使用最少的缓存
ADMIN SET ALL FRONTENDS CONFIG ('sql_cache_manage_num'='100');  
  
-- 缓存机制：超过300s没有访问该缓存，会释放掉它
ADMIN SET ALL FRONTENDS CONFIG ('expire_sql_cache_in_fe_second'='300');

-- 缓存机制：默认单个查询结果超过 3000 行结果时，不创建 SQL Cache。  
ADMIN SET ALL FRONTENDS CONFIG ('cache_result_max_row_count'='3000');  
  
-- 缓存机制：默认单个查询结果超过 30M 时，不创建 SQL Cache。  
ADMIN SET ALL FRONTENDS CONFIG ('cache_result_max_data_size'='31457280');

-- 查询配置示例(填入实际查询的字段)
ADMIN SHOW FRONTEND CONFIG LIKE '%expire_sql_cache_in_fe_second%';

-- 备注：表数据发生了变化，例如执行了 insert、delete、update 或 truncate 等操作，缓存会失效，查询到的依然是最新数据

3.集群健康状态查询

主要查看Alive字段，为True则表示为工作状态

SHOW FRONTENDS;
SHOW BACKENDS;

4.设置最长执行时间

-- 查询 SQL 执行最大时间
SHOW VARIABLES LIKE 'max_execution_time';
--  设置SQL 执行最大时间 （1500s , 即 25min）
SET GLOBAL max_execution_time = 1500000;

六、数据库日志查询

访问：http://FE节点的IP地址:8030 （默认是8030端口）

七、注意事项及技巧

1.写sql时，注意默认表和字段名称都严格区分大小写

2.一些Sqlserver有但是Doris没有的语法：

(nolock)
[ ]
插入语法时候从，Values内部的 N'中文' 要改为 '中文'

3.不支持多表事务

4.不支持DUPLICATE KEY UPDATE语法（Mysql重复键更新）

5.常查字段排其他字段前面：doris默认创建前缀索引，按照排序列的前 36 个字节作为索引键
6.Unique模式建表：指定UNIQUE KEY唯一键和DISTRIBUTED分桶，可以都用联合主键字段，查询更快
7.Unique模式下插入：插入重复主键不报错，会更新
8.自增插入：由于分区原因，Id自增可能不连续，忽大忽小；插入可指定自增字段的值（例如ID=10000）
9.布隆过滤器：适合联合主键字段