CODEWITHSUNDEEP
mysqlsql
Teo
Teo

Reputation: 3213

Determine storage requirements for a MySQL row

Hi I obtained this error with a new table on my web-app:

Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs

So I reduced the lenght of some varchar column in order to stay below that value.

I would like to know how many bytes could be used by a full populated row.

Is there a command to know: how many byte are still free before reach 65535 in a row?

Upvotes: 0

Views: 96

Answers (1)

LSerni
LSerni

Reputation: 57418

The method below is incomplete since the CASE only covers those types I needed (see here for the others). The other types get a "8" which is usually okay, unless you have CHAR or TEXT or BLOB columns. TEXT and BLOB count for 256 bytes each.

Everybody is welcome to contribute :-)

SELECT COLUMN_NAME, COLUMN_TYPE,
CASE SUBSTRING_INDEX(COLUMN_TYPE,'(',1)
WHEN 'decimal' THEN FLOOR((NUMERIC_PRECISION+8)/9)*4
WHEN 'integer' THEN 4
WHEN 'varchar' THEN CHARACTER_OCTET_LENGTH
WHEN 'bits' THEN FLOOR((NUMERIC_PRECISION+7)/8)
WHEN 'float' THEN 8
ELSE 4
END AS size
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA='<the name of your database>'
AND TABLE_NAME='<the name of your table>';

and so:

SELECT SUM(size) FROM (
    SELECT COLUMN_NAME, COLUMN_TYPE,
    CASE
    ...
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA='<the name of your database>'
    AND TABLE_NAME='<the name of your table>'
) AS result;

Upvotes: 2

Related Questions