mysql determine storage size of a field

i would like to know if there is a mysql command to determine the curre开发者_运维问答ntly used actual storage size of a mysql field.

Maybe something like "show used_storage myfield FROM ... WHERE ..."

Oddly I couln'd find anything like this.

(The same for postgresql would be interessting too)

Thanks a lot!

For PostgreSQL you can use

SELECT your_column, pg_column_size(your_column)
FROM your_table

A complete list of this kind of functions is in the manual
http://www.postgresql.org/docs/current/static/functions-admin.html

If you are using a character data field, the simplest would be

SELECT SUM(LENGTH(myField)) From MyTable

If you are using some numerical data type, you could just get a count on the rows, and multiply it by the size, for instance 4 for INTEGER, or 8 for DOUBLE.

It won't reflect the actual size taken up on disk however. This is probably a lot more difficult to find out. And even if it was accurate, the actual size of the table could be much more than the total size of all the columns due to fragmentation and other factors.

You can get the size (and index size) of an entire table with:

SHOW TABLE STATUS LIKE 'MyTable';

If you copy out just the column you want to a new temporary table, you may be able to get a more realistic view of how much space your field is actually taking up on disk.

MySQL documents space utilization:

http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html