我创建了一个包含两个表的SQLite DB : day和data。第一个表包含具有id的每一天的日期,第二个表存储具有该日期id的每一天的相应数据。
是否可以获取表数据的大小(以kb为单位),或者可能仅获取每天数据的大小?
发布于 2019-12-18 06:12:08
是否可以获取表数据的大小(以kb为单位),或者可能仅获取每天数据的大小?
每个表的大小不容易,如果有的话。
通过使用length()函数,您可以收集存储的数据大小的近似值。
对于示例,(注意,关于长度的一个非常简单的示例,因为这取决于字符编码,例如在UTF-8中,一个字符可以占用1-4个字节),并根据您的描述(2个tables 和data)
DROP TABLE IF EXISTS day;
DROP TABLE IF EXISTS data;
CREATE TABLE IF NOT EXISTS day (id INTEGER PRIMARY KEY, date TEXT UNIQUE);
CREATE TABLE IF NOT EXISTS data(id INTEGER PRIMARY KEY, dateofdata_reference INTEGER REFERENCES day(id) ON DELETE CASCADE ON UPDATE CASCADE, datacolumn1 TEXT, datacolumn2 TEXT);
/* Add 3 days */
INSERT INTO day (date) VALUES('2019-01-01'),('2019-01-02'),('2019-01-03');
/* Add some data to each day assuming id of a day as 1,2 and then 3 for the 3 days */
INSERT INTO data (dateofdata_reference, datacolumn1,datacolumn2) VALUES
(1,'A','XYZ'),(1,'ABC','UVW'),(1,'ABCD','RST') /* day 1 */
,(2,'Z','ZYX'),(2,'Y','WVU'),(2,'ZZ','TSR'),(2,'YYY','QPO') /* day 2 */
,(3,'J','1234567890') /* day 3 */
;
/* Combined length of data for entire table (excludes 1 and dateofdata_reference) */
SELECT sum(length(datacolumn1)) + sum(length(datacolumn2)) AS size FROM data;
/* Combined length of data and other stats per day (excludes id and dateofdata_reference) */
SELECT
date,
sum(length(datacolumn1)) + sum(length(datacolumn2)) AS size,
count() AS rowsPerDay,
avg(length(datacolumn1) + length(datacolumn2)) AS averageRowSize,
avg(length(datacolumn1)) AS averageDataColumn1Size,
avg(length(datacolumn2)) AS averageDataColumn2Size
FROM data AS a
JOIN day ON dateofdata_reference = day.id
GROUP BY dateofdata_reference;
/* Cleanup demo environment */
DROP TABLE IF EXISTS data;
DROP TABLE IF EXISTS day;第一个(大约表大小)查询的结果是:-

第二个(每天的统计数据)结果是:-

https://stackoverflow.com/questions/59373041
复制相似问题