mysql中的存储字符串,在MySQL中存储字符串作为varchar的内存使用

╰半橙微兮° 2023-10-03 11:16 7阅读 0赞

![Image 1][]

I've begun to get very interested in the memory usage of MySQL. So I'm looking at this here:

I get very excited about the prospect of saving memory by (for example) needing only a signed smallint where I was using an unsigned int in many places. Then I read about varchars...

"VARCHAR(M) - Length + 1 bytes if column values require 0 – 255 bytes"

What?! Now it appears to me as though storing a single varchar would use up so much memory, that I may as well not even get excited with my int vs. smallint because it's vastly overshadowed by the varchar field. So I come here asking if this is true, because it simply can't be? Are varchars really that terrible? Or should I really not be getting excited at all for my smallint discovery?

edit: Sorry! I should've been more clear. So, let's say I store a varchar with 7 characters, meaning 8 bytes. That means, then, that it uses the same as a number stored in a BIGINT column? That's what I'm concerned about.

解决方案

What this is saying is that for a given string length, the amount of storage used is equal to the length of the string in bytes, plus one byte to tell MySQL how long the string is.

So for instance, the word "automobile" is 10 bytes (1 for each character), so if it is stored in a varchar column it will take up 11 bytes. 1 for the number 10 , and 1 each for each of the characters in the string.

The storage requirements depend on these factors:

\-The actual length of the column value

\-The column's maximum possible length

\-The character set used for the column, because some character sets contain multi-byte characters

For example, a VARCHAR(255) column can hold a string with a maximum length of 255 characters. Assuming that the column uses the latin1 character set (one byte per character), the actual storage required is the length of the string (L), plus one byte to record the length of the string. For the string 'abcd', L is 4 and the storage requirement is five bytes. If the same column is instead declared to use the ucs2 double-byte character set, the storage requirement is 10 bytes: The length of 'abcd' is eight bytes and the column requires two bytes to store lengths because the maximum length is greater than 255 (up to 510 bytes).

[Image 1]: