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 4 of 4
  1. #1
    New to the CF scene
    Join Date
    Dec 2012
    Posts
    4
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Question When I create an attribute column with "MEDTEXT" does mySql Reserve 16 chars each row

    When I create an attribute column with "MEDTEXT" does mySql Reserve 16 chars for each row?
    I am making an "account system" and I have Meta data that can be associated with users.
    Some meta data is 1 or 0. But sometimes it can be a lengthy description for the user.
    That's a lot of space to reserve for parts of the table that don't need it.

    Thank you for the help.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,133
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    Depends on how you specify the column.

    If you do
    Code:
    CREATE TABLE yourTableName (
        medtext CHAR(16),
        ...
    );
    Then MySQL indeed reserves space for exactly 16 characters in every record. And you will get an error (or warning, depending on how you set up MySQL) when you try to put more than 16 characters into the field.

    However, if you use
    Code:
        medtext VARCHAR(16),
    Then MySQL will store only as many characters as you actually put into the field. Again, though, 16 will be the maximum allowed. You could do
    Code:
        medtext VARCHAR(255),
    and still MySQL will only need as much space as you actually use.

    NOTE: This is not quite true. VARCHAR, because it does use a variable amount of space, incurs OVERHEAD in the form of a hidden length value that remembers how much data is stored. So if you only need to store, say, up to 4 characters, it will be more efficient to use CHAR(4). But a caution: CHAR() of any size *PADS* your text with SPACES up to the given size.

    In general, CHAR() is much much less often used than VARCHAR().

    N.B.: The above applies pretty much unchanged to all relational databases: MySQL, SQL Server, Oracle, etc.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    ModestLibrarian (12-29-2012)

  • #3
    New to the CF scene
    Join Date
    Dec 2012
    Posts
    4
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Thumbs up

    Wow. That was jam packed. Thanks!
    I have moved away from meta-data though because what I'm providing is better to be manually configured on that level than some API.
    This information is still very useful though, thank you.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,133
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    I don't know how you can "move away from metadata." Virtually any system, and especially any software system, has metadata, either explicitly declared and created or at the least implied by its very structure.

    Even your manual configuration implies that there is metadata involved. In your head, if no place else.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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