List:General Discussion« Previous MessageNext Message »
From:Thimble Smith Date:February 8 2000 5:52am
Subject:Re: data type questions
View as plain text  
On Mon, Feb 07, 2000 at 08:29:34PM -0800, M. Erickson wrote:
>email addresses (right now I'm using varchar(30))

If you're using varchar anyway, you might as well go a little bit
larger than 30 - you won't lose any space on disk, and it'll just make
MySQL use a little more RAM when you use that field.  You can go up
to varchar(256), but probably something like varchar(100) is more than
enough.

>IP's (using varchar(15))

You could use a char(15) instead.  Or you could convert the IP into
a 32-bit integer and store it in an INT UNSIGNED field (takes up 4
bytes instead of 15).

>also, does MySQL have a binary datatype? if so, is it wise to use it?

Sure, it's got BLOB and it's got (VAR)CHAR BINARY types.  It's wise to
use them wisely.

>Are enums a good idea?

Sure, they work fine.  You just have to use them for what they're meant
for.  SQL snobs don't like them, but really they're handy.

You can use an ENUM:

CREATE TABLE foo ( ..., shirt_type ENUM('dress', 'polo', 't-shirt'), ...);

...

SELECT * FROM foo WHERE shirt_type = 'polo';

or you can do:

CREATE TABLE foo ( ..., shirt_type TINYINT UNSIGNED, ...);
CREATE TABLE shirt_types (
    id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name CHAR(10) NOT NULL,
    KEY name_k (name)
);
INSERT INTO shirt_types (name) VALUES ('dress', 'polo', 't-shirt');

...

SELECT foo.* FROM foo, shirt_types st WHERE st.name = 'polo' AND foo.shirt_type = st.id;

Now you see why ENUM can be useful....

Tim
Thread
data type questionsM. Erickson8 Feb
  • Re: data type questionsThimble Smith8 Feb