MySQL Data types and sizes

If you remember all the way back to how the coding lessons in C started, they started by looking at different datatypes, like ints long int etc, each requiring different memory spaces or being able to store different sized numbers.

The same thing is true in MySQL.
MySQL is a database server, often whenever you're making a website you have a few diffeerent things to think about.

Lets imagine you;re making an on line magazine, or a piece of forum software, you;re going to have authors, (that's a relatively short bit of text, -who's name is over 20 letters?)
but then you also have article text, or posts etc that's going to be a fairly hefty chunk of text.

So lets look at the kind of thing you'll use to have article titles, or by lines, that's likely to be a varchar type.
Varchar is like a string, you specify how much text you want to be able to store in the field. so for a name you may decide that 20 characters is enough, and you;d specify varchar(20).

for storing text there are a few different types of data field.

Notably these don't hold a number of characters, they actually hold an amount of data which is measured in bytes.
(this is important as you might be using a double byte character set -then you can only store half as much text by character count.

So, the data types are
Tinytext which can store 256 bytes,
Text which can store 65,535 bytes (64Kb)
Mediumtext -which can store 16,777,215 bytes (16MB)
Longtext 4,294,967,295 bytes that's a huge 4GB!!!

so how does this work? a 4GB chunk of text is huge to just be a row on a table.

well, the MyISAM tables actually have a maximum size of 65,535 bytes (that means a row can only be that size.) -but surely if a text field can be that by itself.

What happens is that a pointer is stored with the row data that points to the large text chunk, that file pointer is anywhere between 1 - 4 bytes. That way large data is store outside of the normal row.