SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Thread: MySQL Datatypes

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2001
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL Datatypes

    Hello-

    i have created the following columns in my table:

    apn INT(11) ---for a 6-10 digit number
    company INT(1) ---for a 1 digit company code
    property INT(11) ---for a 6-8 digit number
    annualized DECIMAL (7,2) ---for a $ amt.
    fee DECIMAL (6,2) ---for a $ amount

    after i create the table i attempt to load a .csv like so:
    LOAD DATA LOCAL INFILE '/nameoffile.csv' INTO TABLE table_name

    the message says nothing is dropped, but there are tons of warnings? i check the table and the only thing that shows is the apn column.

    am i declaring the datatypes properly?

    do i already have to have a column with primary key, or can i add that column later?

    i'm new to this.. thanks for your reply.

  2. #2
    What? Maelstrom's Avatar
    Join Date
    Oct 2001
    Location
    Whistler BC originally from Guelph Ontario
    Posts
    2,175
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    apn INT ---for a 6-10 digit number 
    company tinyINT ---for a 1 digit company code 
    property INT ---for a 6-8 digit number 
    annualized DECIMAL (7,2) ---for a $ amt. 
    fee DECIMAL (6,2) ---for a $ amount

    I have never used decimal but according to the docs that looks right... Use php or another way to check the length of the variable.

    Hope that works.
    Maelstrom Personal - Apparition Visions
    Development - PhP || Mysql || Zend || Devshed
    Unix - FreeBSD || FreeBsdForums || Man Pages
    They made me a sitepoint Mentor - Feel free to PM me or Email me and I will see if I can help.

  3. #3
    SitePoint Wizard gold trophysilver trophy
    Join Date
    Nov 2000
    Location
    Switzerland
    Posts
    2,479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you're loading a csv file, you need to be careful that the data there matches the requirements for each data type (e.g. may be it contains decimals where the column requires INTs). Excel is a a really good tools for massaging such data before loading into MySQL.

    As to the primary key, it would be a good idea to define it before loading the data. If the first number (apn) is unique for every new row, that could be the primary key. Otherwise you need a new column, which means further massaging of that csv file.


Bookmarks

Posting Permissions

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