Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Mar 2006
    Posts
    459
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Question About Column

    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?

  • #2
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    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.

    HTH!

  • #3
    Regular Coder
    Join Date
    Mar 2006
    Posts
    459
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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...
    Attached Files Attached Files

  • #4
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    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'.

    Why?

    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
    NULL -- NULL, NOT NULL
    Default -- DEFAULT value IF 'NOT NULL'
    Extra -- auto_increment, zerofill

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

    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...

  • #5
    Regular Coder
    Join Date
    Mar 2006
    Posts
    459
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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?

  • #6
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    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.).


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •