Hey, i was just wondering what the best way to know the field length to use in MySQL would be. I know in MyBB different fields have different lengths, and I've always just done int(11) and varchar(255) for integers, and strings, and I know this is bad practice. Thanks!
If you think the int will be more than 11 then increase it, same goes for varchar. All depends on what is being inserted to each field. For example if you have a field called "News" where you want to save news stories submitted by your users, then a varchar of 255 won't be much addition, as each news story will tend to be much larger. You could use a "varchar(5000)" in this instance, to allow big news posts.
(2012-07-13, 07:07 PM)Frank.Barry Wrote: [ -> ]If you think the int will be more than 11 then increase it, same goes for varchar. All depends on what is being inserted to each field. For example if you have a field called "News" where you want to save news stories submitted by your users, then a varchar of 255 won't be much addition, as each news story will tend to be much larger. You could use a "varchar(5000)" in this instance, to allow big news posts.
Or you could just use the text field, which allows input of any length and is designed for exactly this purpose.
Generally, just pick a reasonable standard (255 for VARCHAR is fine, I use 200), and stick to it, unless you know that it won't suit for a given field. For example, if you're storing passwords, chances are the hash will be a fixed length, so you can set the field size to match.
Use the right tool for the job: TEXT for variable amounts of text, VARCHAR for limited amounts of text, INT(x) UNSIGNED for positive-only numbers like IDs (where x is probably going to be 5-10 because anything higher is out of range for INT and anything lower is a waste of space), and INT(x) for positive and negative numbers like rep (where you could probably use SMALLINT(3-5) anyway).
How do I know? The 'x' in T(x) is how many digits/characters to show, so knowing the ranges of the types tells what to use.
INT is a 4-byte integer, meaning it's max value is 4,294,967,295 when UNSIGNED, and half that otherwise. 4,294,967,295 is 10 digits, so that's what the max 'x' is for INT(x).
SMALLINT is a 2-byte integer, meaning it's max value is 65,535 UNSIGNED, and 32,767 otherwise. 65,535 is 5 digits, so that's the max for that.
VARCHAR is any length you want up to around 65,535, but really it should be used for short strings like titles, password hashes (Never ever EVER store a password in plain text in the database. EVER. Don't do it. If anyone wants you to do it, kick them in the balls.), user names, etc. They're stored with the rest of the data, so when the DB server has to search through the rows to find something, it has to scan over all of the VARCHARs in the table. TEXT is stored separately with just a reference, so the server can ignore it if it's not what the server wants.
TEXT can be any length you want, within reason (obviously if you have a 30GB HDD then the TEXT can only be 30GB minus whatever is already in use). In fact, it ignores the (x) since it's defined to be a variable amount of space (and I think throws an error if you try to limit it). There are also MEDIUMTEXT, LONGTEXT and TINYTEXT but I have no clue the difference as the reference I'm using doesn't explain. I would guess MEDIUMTEXT stores 4GB and TINYTEXT stores 255 characters, but that's just guessing.
(2012-07-13, 10:24 PM)Firestryke31 Wrote: [ -> ]There are also MEDIUMTEXT, LONGTEXT and TINYTEXT but I have no clue the difference as the reference I'm using doesn't explain. I would guess MEDIUMTEXT stores 4GB and TINYTEXT stores 255 characters, but that's just guessing.
The reference I have here gives the following:
TINYTEXT: 0 to 255 characters.
TEXT: 0 to 65535 characters. For lengths greater than this it becomes MEDIUMTEXT or LONGTEXT as required.
MEDIUMTEXT: 0 to 16777251 characters.
LONGTEXT: 0 to 4294967295 characters.