SitePoint Sponsor

User Tag List

Results 1 to 18 of 18
  1. #1
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    when data is too long

    If data is too long for one of my table columns designated TEXT, is there any solution or should I give up in saving the long data in the column?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    TEXT: maximum length of 65 kilobytes
    MEDIUMTEXT: maximum length of 16 megabytes
    LONGTEXT: maximum length of 4 gigabytes

    straight out of the manual, joon -- you could have looked this up easily yourself
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    straight out of the manual, joon -- you could have looked this up easily yourself
    I feel like that someone is chasing me.

  4. #4
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    How can I check whether the column is TEXT, MEDIUMTEXT, or LONGTEXT?
    Last edited by dotJoon; Feb 1, 2009 at 21:21.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you're kidding, right? you don't know how to look at your own table?

    how long have you been working with mysql, joon?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you're kidding, right?
    I am serious.
    I am usually too serious.

    Quote Originally Posted by r937 View Post
    you don't know how to look at your own table?
    I installed mySQL 5.0 by one click. I work visual mySQL.
    I do SQL code for selecting, inserting, updateing, deleting records.

    BUT I do work visually for table creating, adding column instead of SQL coding

    Quote Originally Posted by r937 View Post
    how long have you been working with mysql, joon?
    Since I became a member of sitePoint. Wow, it was over 6 years. Time flies! Especially I met you.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    please look through your many hundreds of posts, and their replies, to find out how to display your table

    there is a very simple command that will allow you to do this

    if you get stuck, please read da manual
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    TEXT: maximum length of 65 kilobytes
    MEDIUMTEXT: maximum length of 16 megabytes
    LONGTEXT: maximum length of 4 gigabytes
    As I test, TEXT allows about 40,000 korean characters and LONGTEXT allows about 62,000 characters.

    Since 4 gigabytes is bigger than two times of 65 kilobytes, LONGTEXT should allow more than 80,000 characters (40,000 *2), shouldn't it?

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes it should
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    As I restart mySQL server and even I restart my computer, LONGTEXT can still contain about 62,000 characters.

    It can't contain over 63,000 characters.

    What's wrong in my mySQL in your guess?
    (It's should contains much more than that.)

  11. #11
    SitePoint Addict aguroyz's Avatar
    Join Date
    Jan 2009
    Location
    Konoha Fire Country
    Posts
    311
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try using the Blob datatype...

    but i'm not quite sure if it could store text...
    Uniquely FILIPINO... See how talented and creative Filipinos are.
    http://www.smalltym.com
    Custom Web Designs:
    http://proweaver.com

  12. #12
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by aguroyz View Post
    try using the Blob datatype...
    As I test blob, I guess Blob can't store text.

  13. #13
    SitePoint Addict aguroyz's Avatar
    Join Date
    Jan 2009
    Location
    Konoha Fire Country
    Posts
    311
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hhmmm, that seemed to be a problem...

    try creating two columns to cater for that if one can't store all..

    I haven't really encountered something like this before..
    Uniquely FILIPINO... See how talented and creative Filipinos are.
    http://www.smalltym.com
    Custom Web Designs:
    http://proweaver.com

  14. #14
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    the problem is not the size of your field, but the largest amount of data you can send to your mysql server in one command. the setting is called max_packet_size. if you look at your server variables in mysql administrator, you can find that value of max_packet_size. you have to make sure that your queries and commands are at least 1000 bytes or so smaller than max_packet_size.

    one way around this limit is to break your string in to multiple statements. for example, you can do this:
    Code:
    insert joonTable (data)
    values ('this is the beginning...');
    
    set @id = last_insert_id();
    
    update joonTable
    set data = concat(data, '...of a really long sentence.')
    where id = @id;
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  15. #15
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Good catch longneck!

  16. #16
    SitePoint Addict aguroyz's Avatar
    Join Date
    Jan 2009
    Location
    Konoha Fire Country
    Posts
    311
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    the problem is not the size of your field, but the largest amount of data you can send to your mysql server in one command. the setting is called max_packet_size. if you look at your server variables in mysql administrator, you can find that value of max_packet_size. you have to make sure that your queries and commands are at least 1000 bytes or so smaller than max_packet_size.

    one way around this limit is to break your string in to multiple statements. for example, you can do this:
    Code:
    insert joonTable (data)
    values ('this is the beginning...');
    
    set @id = last_insert_id();
    
    update joonTable
    set data = concat(data, '...of a really long sentence.')
    where id = @id;

    thanks for this, but I have a question though,

    why are they allowing variables w/c are bigger than max_packet_size if it can't process that variable??

    Is the max_packet_size the same w/ every database servers?? and

    Is there a way we could adjust the max packet size??
    Uniquely FILIPINO... See how talented and creative Filipinos are.
    http://www.smalltym.com
    Custom Web Designs:
    http://proweaver.com

  17. #17
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    yes, max_pack_size is a configuration setting. often times the limit is imposed to prevent people from transferring hundreds of megabytes or more at a time to the server when there is no good reason, except for a bug. most shared hosting accounts limit you to a couple of megabytes.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  18. #18
    SitePoint Addict aguroyz's Avatar
    Join Date
    Jan 2009
    Location
    Konoha Fire Country
    Posts
    311
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oh, I see.. thanks
    Uniquely FILIPINO... See how talented and creative Filipinos are.
    http://www.smalltym.com
    Custom Web Designs:
    http://proweaver.com


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
  •