View Full Version : Question About Column

04-06-2007, 02:54 PM
I am going to import about 200 generated codes into a MySQL table... Just had a queation, the codes will look like this as an e.g. 7dYhsj8. So would I just make then 'Text' as the data type and Not Null?

04-06-2007, 03:02 PM
How long will your character strings be?

If they will be less than 255 characters, use the VARCHAR() data type. Less memory overhead than the TEXT data type.

Use a spreadsheet program to get the longest length (7dYhsj8 = 7) and then tailor your VARCHAR() limit to the longest field rounded up to the nearest factor of 8 (8 bits = 1 byte logic).

FOR EXAMPLE: If your longest is 7, do a VARCHAR(8). You are not losing anything for the 1 extra bit length, and you will be less likely to overstep your limit bounds on future inserts.

NOT NULL is up to you, but think about future INSERTS. Will this code be required on all records? If so, then NOT NULL is perfect. If not, DEFAULT NULL or DEFAULT '' is the way to go.


04-06-2007, 03:13 PM
Actually they will be (hfgsyt7d = 8) so would I make VARCHAR(9) why do I need to add the extra (1) just out of curiosity? Sorry didn't understand that. Also I am just going to import them each time I need another 200 from an Excel file, as the user types in one of the codes via a PHP form they will be removed from the database, so do you think NOT NULL?

Also if anyone of you are familiar with phpMyAdmin and wouldn't mind taking a look at the snapshot, could you possibly tell em if this looks correct? It seems to have a lot of options on the bottom half which I am not familiar with... I made 'id' the primary key if you can see that in the snapshot...

04-06-2007, 04:41 PM
Since apparently I can't count (need more coffee), VARCHAR(8) would be perfect. And since, you are always adding data to that field in your database, then you are correct in making that field NOT NULL.

As a rule of thumb of K.I.S.S., why not name the 'id' field to something slightly more descriptive? So, if these codes are the only thing in the table, name it 'CodeID'.


So let's say that later on down the road you want to run a comparison of all the codes in a JOIN to another table. They both have 'id' as the auto_inc field. You forget to use aliases. Which ID is which? Which ID is mapped to which table? More descriptive ID fields "KEEP IT SIMPLE SILLY" (K.I.S.S.).

Your ID field needs to be _AT_LEAST_ INT(), as TINYINT() only goes to 127 (and since each load is 200, you won't capture 73 of the codes). You need to use a higher value.

Default AUTO_INC fields should be BIGINT() UNSIGNED. UNSIGNED means that the data in the field cannot be negative.

I hand code my tables, so I don't use phpMyAdmin, but in the listing it seems like:

Field -- Field name
Type -- Data type
Length -- max length of field
Attributes -- SIGNED, UNSIGNED, BINARY, etc
Default -- DEFAULT value IF 'NOT NULL'
Extra -- auto_increment, zerofill

IMAGES are Key types:
(i don't know this one... possibly SPATIAL??)

Comments -- Comment on the field
Everything else below, I know what they mean, but they don't pertain to this discussion.

HTH, and helps clear up some of the confusion...

04-06-2007, 04:45 PM
Great thanks! Also I have been playing around with the table and obviously the auto_increment keeps adding. If I delete all the data from the table how can I reset the auto_incrememnt?

04-06-2007, 04:50 PM
You need to use 1 command to reset the fields and empty the table...

mysql> TRUNCATE TABLE `table_name`;

What this command does is actually quite ingenious. It makes a temporary copy of the table structure, drops the current table, then rebuilds the table off the saved structure. Hence, it dumps all your data and resets the AUTO_INC field in 1 blow.

Now, there is a way to reset the AUTO_INC fields using the ALTER TABLE statement, but that's a pain. (I can list it out, but then read previous post - K.I.S.S.).