0

I need to know how many bytes are occupied by a column in MySQL.

Consider the following schema -

+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id    | bigint(20) unsigned | NO   | PRI | NULL    |       |
| data  | longblob            | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+

Ignore id field, lets talk about data field. Consider these tuples -

+-------+---------------------+
| id    | data                |
+-------+---------------------+
|   1   | {ab₹}               |
|   2   | {ab}                |
+-------+---------------------+

So, what I need is size in bytes not no of characters like-

+---------------------+
| size_in_bytes       | 
+---------------------+
|                   7 |     // {->(1), a->(1), b->(1), ₹->(3), }->(1)
|                   4 |     // {->(1), a->(1), b->(1), }->(1)
+---------------------+

After hours of search I found few functions which only result in no of characters.

select OCTET_LENGTH(data) from table_name;
+--------------------+
| OCTET_LENGTH(data) |
+--------------------+
|                  5 |
|                  4 |
+--------------------+

SELECT LENGTH(data) from table_name;
+--------------+
| LENGTH(data) |
+--------------+
|            5 |
|            4 |
+--------------+

SELECT char_length(data) from table_name;
+-------------------+
| char_length(data) |
+-------------------+
|                 5 |
|                 4 |
+-------------------+

Similar Question -> How to get size of column in mysql table but none of the answers results in bytes.

How to get the sizes of the tables of a MySQL database? and this is for size of the table.

MySQL version -> 8.0

enter image description here

Shadow
  • 33,525
  • 10
  • 51
  • 64
Sathvik
  • 565
  • 1
  • 7
  • 17

1 Answers1

0

Count bits

select BIT_LENGTH (N'{ab₹}')/8;

returns 7.0000

db<>fiddle

Serg
  • 22,285
  • 5
  • 21
  • 48
  • I swear in mysql8 o/p for ```select BIT_LENGTH (N'{ab₹}')/8;``` is ``` 5.0000```. Just now tested. – Sathvik Jun 25 '21 at 09:42
  • @Sathvik, see dbfiddle. – Serg Jun 25 '21 at 09:45
  • Yes it's working in dbfiddle thanks for helping. But my terminal is resulting wrong answer any idea – Sathvik Jun 25 '21 at 09:50
  • Probably different collation ? `SELECT @@character_set_database, @@collation_database` – Serg Jun 25 '21 at 09:54
  • ```| utf8mb4 | utf8mb4_0900_ai_ci |``` how to set it – Sathvik Jun 25 '21 at 10:05
  • Hmm, what is `hex(N'{ab₹}')` ? is it '7B6162E282B97D' like in MySql behind the fiddle? – Serg Jun 25 '21 at 10:07
  • I got ```7B61623F7D``` – Sathvik Jun 25 '21 at 10:12
  • Is there any way to get whole column size(bytes occupied) for BLOB field using information_schema. No need for individual row size, actually I need the whole column size(in bytes). – Sathvik Jun 25 '21 at 10:20
  • BLOB size is not part of a table schema. All BLOBs in a db share the same max size determined by the type itself. Row's size is varying. – Serg Jun 25 '21 at 10:27
  • Best thing is to redesign my db. I will create an extra column for size. While sending data to db I will compute it and send it. – Sathvik Jun 25 '21 at 10:32
  • @Sathvik You have a character encoding issue, all your characters are encoded on a single byte. The calculation method suggested here (or just using the lenght() function) is fine, your data is not fine. – Shadow Jun 25 '21 at 10:49