MySQL テーブルの型定義を使って行サイズを計算したい

MySQL

Last updated at 2025-09-17Posted at 2025-09-04

MySQL テーブルの型定義を使って行サイズを計算したい

MySQL（特に InnoDB）には 1行あたり最大 65,535 バイトという制限がある。DDL（定義）段階から行サイズを概算すれば、設計ミスや ERROR 1118 の発生を防ぎやすくなる。
また、上記上限とは別に、InnoDB ストレージエンジン独自の制約として「1行のローカル部分は 約 8KB（8,126 バイト程度） まで」という制限がある。
information_schema.columns を使って、行サイズのDDLベースの見積もりができるSQL。使いたい時にいつも見つからないので、ここに残す。

背景：なぜ行サイズを計算すべきか？

65,535 バイト制限

MySQL は 行の最大サイズが 65,535 バイトに制限されている。この制限を超えると ERROR 1118 などが発生する場合がある。(dev.mysql.com)
VARCHAR や TEXT のような可変長型は、文字セット、プレフィックス、行フォーマットによって行サイズに大きく影響する。
TEXT / BLOB 型は、行内に残るのは約 9〜12 バイトのみで、本体はオフページに保存される。(dev.mysql.com)

8,126 バイト制限

InnoDB ではデータを「ページ（デフォルト16KB）」単位で管理している。1ページには複数行が格納されるため、1行がページ全体を専有しないように 「1行は1ページの半分弱まで」 という制約がある。
VARCHAR / TEXT / BLOB は ROW_FORMAT=DYNAMIC / COMPRESSED の場合、大きな値はページ外（オフページ）に逃がされ、行内には 20バイトのポインタしか残らない。そのため実際には 8126 制約を回避できるケースが多い。
よくあるエラー

ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help.

実際のSQL

参考：Calculate row size and max row size for a table
https://dba.stackexchange.com/questions/114471/calculate-row-size-and-max-row-size-for-a-table

SET @schema       = 'your_schema';
SET @table_filter = 'your_table';  -- 全テーブル対象にするなら NULL

WITH cols AS (
  SELECT
    c.TABLE_SCHEMA, c.TABLE_NAME, c.ORDINAL_POSITION,
    c.COLUMN_NAME, c.DATA_TYPE,
    c.CHARACTER_OCTET_LENGTH, c.CHARACTER_SET_NAME,
    c.NUMERIC_PRECISION, c.NUMERIC_SCALE,
    c.DATETIME_PRECISION, c.IS_NULLABLE
  FROM INFORMATION_SCHEMA.COLUMNS c
  WHERE c.TABLE_SCHEMA = @schema
    AND (@table_filter IS NULL OR c.TABLE_NAME = @table_filter)
),
per_col AS (
  SELECT
    TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE,

    -- (A) 65,535 の論理行サイズに用いる「列寄与」
    CASE DATA_TYPE
      WHEN 'tinyint'   THEN 1
      WHEN 'smallint'  THEN 2
      WHEN 'mediumint' THEN 3
      WHEN 'int'       THEN 4
      WHEN 'integer'   THEN 4
      WHEN 'bigint'    THEN 8
      WHEN 'float'     THEN IF(COALESCE(NUMERIC_PRECISION,24) > 24, 8, 4)
      WHEN 'double'    THEN 8
      WHEN 'real'      THEN 8
      WHEN 'decimal'   THEN
        ((NUMERIC_PRECISION - NUMERIC_SCALE) DIV 9) * 4
        + (NUMERIC_SCALE DIV 9) * 4
        + CEIL(MOD(NUMERIC_PRECISION - NUMERIC_SCALE, 9) / 2)
        + CEIL(MOD(NUMERIC_SCALE, 9) / 2)
      WHEN 'numeric'   THEN
        ((NUMERIC_PRECISION - NUMERIC_SCALE) DIV 9) * 4
        + (NUMERIC_SCALE DIV 9) * 4
        + CEIL(MOD(NUMERIC_PRECISION - NUMERIC_SCALE, 9) / 2)
        + CEIL(MOD(NUMERIC_SCALE, 9) / 2)
      WHEN 'bit'       THEN (COALESCE(NUMERIC_PRECISION,1) + 7) DIV 8
      WHEN 'year'      THEN 1
      WHEN 'date'      THEN 3
      WHEN 'time'      THEN 3 + CEIL(COALESCE(DATETIME_PRECISION,0) / 2)
      WHEN 'datetime'  THEN 5 + CEIL(COALESCE(DATETIME_PRECISION,0) / 2)
      WHEN 'timestamp' THEN 4 + CEIL(COALESCE(DATETIME_PRECISION,0) / 2)
      WHEN 'char'      THEN COALESCE(CHARACTER_OCTET_LENGTH,0)
      WHEN 'binary'    THEN COALESCE(CHARACTER_OCTET_LENGTH,0)
      WHEN 'varchar'   THEN (CASE WHEN COALESCE(CHARACTER_OCTET_LENGTH,0) > 255 THEN 2 ELSE 1 END)
                         + COALESCE(CHARACTER_OCTET_LENGTH,0)
      WHEN 'varbinary' THEN (CASE WHEN COALESCE(CHARACTER_OCTET_LENGTH,0) > 255 THEN 2 ELSE 1 END)
                         + COALESCE(CHARACTER_OCTET_LENGTH,0)
      WHEN 'tinytext'  THEN  9
      WHEN 'text'      THEN 10
      WHEN 'mediumtext'THEN 11
      WHEN 'longtext'  THEN 12
      WHEN 'tinyblob'  THEN  9
      WHEN 'blob'      THEN 10
      WHEN 'mediumblob'THEN 11
      WHEN 'longblob'  THEN 12
      WHEN 'enum'      THEN 2
      WHEN 'set'       THEN 8
      ELSE 0
    END AS col_size_65535,

    -- (B-1) InnoDB 行内“最悪”(可変長が全部行内) の列寄与
    CASE DATA_TYPE
      WHEN 'varchar'   THEN (CASE WHEN COALESCE(CHARACTER_OCTET_LENGTH,0) > 255 THEN 2 ELSE 1 END)
                         + COALESCE(CHARACTER_OCTET_LENGTH,0)
      WHEN 'varbinary' THEN (CASE WHEN COALESCE(CHARACTER_OCTET_LENGTH,0) > 255 THEN 2 ELSE 1 END)
                         + COALESCE(CHARACTER_OCTET_LENGTH,0)
      WHEN 'tinytext'  THEN  9
      WHEN 'text'      THEN 10
      WHEN 'mediumtext'THEN 11
      WHEN 'longtext'  THEN 12
      WHEN 'tinyblob'  THEN  9
      WHEN 'blob'      THEN 10
      WHEN 'mediumblob'THEN 11
      WHEN 'longblob'  THEN 12
      ELSE 0
    END AS col_local_inline_worst,

    -- (B-2) InnoDB 行内“DYNAMIC/COMPRESSED”想定(大きい値は20Bポインタ)
    CASE
      WHEN DATA_TYPE IN ('varchar','varbinary',
                         'tinytext','text','mediumtext','longtext',
                         'tinyblob','blob','mediumblob','longblob')
        THEN 20
      ELSE 0
    END AS col_local_dynamic_ptr,

    -- NULLビット（後で合算：列数/8の切り上げ）
    CASE WHEN IS_NULLABLE='YES' THEN 1 ELSE 0 END AS is_nullable_bit,

    -- 固定長(行内に常に載る)の寄与
    CASE DATA_TYPE
      WHEN 'tinyint'   THEN 1
      WHEN 'smallint'  THEN 2
      WHEN 'mediumint' THEN 3
      WHEN 'int'       THEN 4
      WHEN 'integer'   THEN 4
      WHEN 'bigint'    THEN 8
      WHEN 'float'     THEN IF(COALESCE(NUMERIC_PRECISION,24) > 24, 8, 4)
      WHEN 'double'    THEN 8
      WHEN 'real'      THEN 8
      WHEN 'decimal'   THEN
        ((NUMERIC_PRECISION - NUMERIC_SCALE) DIV 9) * 4
        + (NUMERIC_SCALE DIV 9) * 4
        + CEIL(MOD(NUMERIC_PRECISION - NUMERIC_SCALE, 9) / 2)
        + CEIL(MOD(NUMERIC_SCALE, 9) / 2)
      WHEN 'numeric'   THEN
        ((NUMERIC_PRECISION - NUMERIC_SCALE) DIV 9) * 4
        + (NUMERIC_SCALE DIV 9) * 4
        + CEIL(MOD(NUMERIC_PRECISION - NUMERIC_SCALE, 9) / 2)
        + CEIL(MOD(NUMERIC_SCALE, 9) / 2)
      WHEN 'bit'       THEN (COALESCE(NUMERIC_PRECISION,1) + 7) DIV 8
      WHEN 'year'      THEN 1
      WHEN 'date'      THEN 3
      WHEN 'time'      THEN 3 + CEIL(COALESCE(DATETIME_PRECISION,0) / 2)
      WHEN 'datetime'  THEN 5 + CEIL(COALESCE(DATETIME_PRECISION,0) / 2)
      WHEN 'timestamp' THEN 4 + CEIL(COALESCE(DATETIME_PRECISION,0) / 2)
      WHEN 'char'      THEN COALESCE(CHARACTER_OCTET_LENGTH,0)
      WHEN 'binary'    THEN COALESCE(CHARACTER_OCTET_LENGTH,0)
      ELSE 0
    END AS col_fixed_local
  FROM cols
),
per_table AS (
  SELECT
    TABLE_SCHEMA, TABLE_NAME,
    SUM(col_size_65535)                         AS sum_65535,
    SUM(col_fixed_local)                        AS sum_fixed_local,
    SUM(col_local_inline_worst)                 AS sum_var_inline_local,
    SUM(col_local_dynamic_ptr)                  AS sum_var_ptr_local,
    CEIL(SUM(is_nullable_bit)/8)                AS null_bitmap_bytes,
    COUNT(*)                                    AS column_count
  FROM per_col
  GROUP BY TABLE_SCHEMA, TABLE_NAME
)
SELECT
  TABLE_SCHEMA,
  TABLE_NAME,
  column_count,
  -- 65,535 の論理行サイズ概算（参考値）
  sum_65535 AS est_max_row_size_65535,
  -- InnoDB 行内(8126) 見積り（ヘッダ ≒ 26B ＋ NULLビットマップ）
  (sum_fixed_local + sum_var_inline_local + null_bitmap_bytes + 26)   AS total_local_inline_worst,
  (sum_fixed_local + sum_var_ptr_local    + null_bitmap_bytes + 26)   AS total_local_dynamic_ptr
  
FROM per_table
ORDER BY TABLE_SCHEMA, TABLE_NAME;

型別寄与例（utf8mb4 対応の例）

型	寄与バイト数（計算式）	備考
`VARCHAR(700)`（utf8mb4）	700 × 4 = 2,800 ＋ 2 = 2,802 B	最大バイト長 + プレフィックス
`TEXT` 型	約 10 B	行内には固定少量、本文はオフページ
`INT`	4 B	固定バイトサイズ
`DECIMAL(10,2)` など	計算により異なる	精密に計算される

utf8mb4 は 1 文字あたり最大 4 バイト のため、VARCHAR の最大寄与を見積もる場合は注意が必要である。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up