如何获取表格特定行的字节数大小

huangapple go评论55阅读模式
英文:

How to obtain the size in bytes of a certain specific rows of a table

问题

I have created a project where I will offer several stores the opportunity to publish their products and charge them based on the Megabytes they occupy in storage. For this purpose, I am using an SQL database. My question is, how can I obtain the space occupied by a customer in my database if the "products" table has the following columns: "productId", "customerId", "productName", "productPrice"?

I have tried this but it returns the total size of the entire table and what I want is to get the size of certain rows where "customerId" is equal to the requested.

SELECT (SUM(DATA_LENGTH) + SUM(INDEX_LENGTH)) AS 'bytes'
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'project' AND TABLE_NAME = 'products';

If you have a better idea to solve this I would greatly appreciate it.

英文:

I have created a project where I will offer several stores the opportunity to publish their products and charge them based on the Megabytes they occupy in storage. For this purpose, I am using an SQL database. My question is, how can I obtain the space occupied by a customer in my database if the "products" table has the following columns: "productId", "customerId", "productName", "productPrice"?

I have tried this but it returns the total size of the entire table and what I want is to get the size of certain rows where "customerId" is equal to the requested.

SELECT (SUM(DATA_LENGTH) + SUM(INDEX_LENGTH)) AS 'bytes'
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'project' AND TABLE_NAME = 'products';

If you have a better idea to solve this I would greatly appreciate it.

答案1

得分: 0

以下是翻译好的部分:

如果我们可以考虑行都是相同大小的话,这个方法可能会起作用:

思路是统计每个客户的行数,除以总行数,然后将结果乘以总字节数。

with cte as (
  select count(1) as totalData, s.bytes
  from products
  inner join (
    SELECT (SUM(DATA_LENGTH) + SUM(INDEX_LENGTH)) AS 'bytes'
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = 'project' and TABLE_NAME = 'products'
  ) as s on true
)
select customer_id, (count(1)/totalData)*c.bytes as totalBytes
from products
inner join cte c on true
group by customer_id
英文:

If we can consider that the rows are all the same size, this could work:

The idea is to count the rows per customer, divide by the total number of rows, and multiply the result by the total number of bytes.

with cte as (
  select count(1) as totalData, s.bytes
  from products
  inner join (
    SELECT (SUM(DATA_LENGTH) + SUM(INDEX_LENGTH)) AS 'bytes'
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = 'project' and TABLE_NAME = 'products'
  ) as s on true
)
select customer_id, (count(1)/totalData)*c.bytes as totalBytes
from products
inner join cte c on true
group by customer_id

huangapple
  • 本文由 发表于 2023年5月17日 11:33:58
  • 转载请务必保留本文链接:https://go.coder-hub.com/76268386.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定