Re: ENUM() vs TINYINT

On 9/21/2015 9:03 AM, Richard Reina wrote: I have a column name quarter which I need to have 5 possible inputs; 1, 2, 3, 4, or OT. Because of the OT possibility I am leaning towards ENUM. Hence, I am also thus considering ENUM('first', 'second', 'third', 'fourth', 'overtime') as the input will

Re: ENUM() vs TINYINT

> From: Richard Reina > > I have a column name quarter which I need to have 5 possible inputs; 1, 2, > 3, 4, or OT. Because of the OT possibility I am leaning towards ENUM. > Hence, I am also thus considering ENUM('first', 'second', 'third', > 'fourth', 'overtime') as the input will primarily be

Re: ENUM() vs TINYINT

I'm sure your enum is a fine option, but 0,1,2,3,4 would do the same thing. And you could add a comment to the table to describe it if desired. On Mon, Sep 21, 2015 at 8:03 AM, Richard Reina wrote: > I have a column name quarter which I need to have 5 possible inputs; 1, 2, > 3, 4, or OT. Becaus

Re: Enum issue

Hi Olaf, all ! Olaf Stein wrote: Hi All If I have a column `consent` enum('Y','N','P') default NULL, And I try to insert 'NULL' I get this error: Warning: Data truncated for column 'consent' at row 1 What is the problem there? Double-check your command: 'NULL' is a string of four (4) lett

Re: Enum issue

Hi, Try with, , `consent` enum ('','Y','N','P') , .mysql> desc table; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ . | consent | enum

Re: enum query results strange.

Hi, both queries would have the limit on them, so they would only return 10 rows. but yes, there are probably about 10x as many records with true than with anything else. If there are only six possible values, and one values occurs ten times as often as the other five values, that means it oc

Re: enum query results strange.

The parantheses come from a php function that is writing the statement. i know they aren't needed. I've seen the same results on another table with an enum with values like 'a','b','c','d', so although i haven't modified this specic query to not use the reservered word true. i know the same resu

Re: enum query results strange.

Tanner Postert wrote: so i am doing a query on an enum field: when i do this query: select *, id as vid, user_id as uid from video where (file_complete = 'true') order by undt desc limit 0,10; the results are 0.16 or 0.17 seconds. instead of saying file_complete = 'true. if i say file_complete

Re: enum TRUE/FALSE

TRUE and FALSE are the integers 1 and 0, respectively. ENUMs hold strings which are assigned numbers starting with 1. That means that WHERE enum_col = TRUE will match rows whose enum_col has the *first* value defined in the ENUM list. Also, every ENUM has the special error value '' in positio

Re: enum TRUE/FALSE

I think you started with good advice then took a strange turn. Chris Blackwell wrote: If you want an enum to have the possible values of NULL or 1 alter table `Associate` modify `Active` enum('1'); from the mysql manual http://dev.mysql.com/doc/mysql/en/ENUM.html If an ENUM column is declared to a

Re: enum TRUE/FALSE

In article <[EMAIL PROTECTED]>, [EMAIL PROTECTED] writes: > I would suggest that if you want to compare against FALSE that you make > that one of your enumerated values. I would also make FALSE your default > value and the field not nullable. That way you don't have 3 possible > values to compa

RE: enum TRUE/FALSE

ssage- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 10, 2004 10:22 AM To: Scott Hamm; 'Mysql ' (E-mail) Subject: RE: enum TRUE/FALSE [snip] mysql> select count(*) from Associate where Active=FALSE; mysql> select count(*) from Associate where Acti

RE: enum TRUE/FALSE

[snip] mysql> select count(*) from Associate where Active=FALSE; mysql> select count(*) from Associate where Active=TRUE; [/snip] Why don't you set enum('TRUE','FALSE')? I ask this because normally you would query, when using NULL (all caps), WHERE Active IS NULL or IS NOT NULL. I believe that yo

Re: enum TRUE/FALSE

I would suggest that if you want to compare against FALSE that you make that one of your enumerated values. I would also make FALSE your default value and the field not nullable. That way you don't have 3 possible values to compare against in your field (null, empty string, and 1). If you need

RE: enum TRUE/FALSE

If you want an enum to have the possible values of NULL or 1 alter table `Associate` modify `Active` enum('1'); from the mysql manual http://dev.mysql.com/doc/mysql/en/ENUM.html If an ENUM column is declared to allow NULL, the NULL value is a legal value for the column, and the default value is

RE: enum TRUE/FALSE

#x27;null','1') | YES | | null | | | Active | enum('null','1') | YES | | NULL | | | Creator | varchar(8) | | || | | NewAssociateDate | date | | | -00-00 | | +------+-

RE: enum TRUE/FALSE

[snip] I'm trying to figure out how to make Active's null as FALSE and '1' as TRUE, in enum point of view | Active | enum('','1') | YES | | NULL| | [/snip] I have not tested this but have you tried enum('NULL', '1') ? -- MySQL General Mailing List For list archives: http://

Re: Enum or Int

Michael Dykman wrote: I hope I'm not opening an old can of worms here, but there are some design trade-offs in this decision. ENUM has the strong advantage of being able to constrain the contents to the specific expected values. It is not possible for an application insert an illegal value wherea

Re: Enum or Int

I hope I'm not opening an old can of worms here, but there are some design trade-offs in this decision. ENUM has the strong advantage of being able to constrain the contents to the specific expected values. It is not possible for an application insert an illegal value whereas using INT one would

Re: Enum or Int

There was a discussion about this topic at past. Check the previous posts.As a simple answer they are technically the same. Use what ever you want.. Best Regards, Cemal Dalar a.k.a Jimmy System Administrator & Web Developer http://www.dalar.net - Original Message - From: "Salzgeber Oliv

Re: ENUM vs TINYINT

uot;Cemal Dalar" <[EMAIL PROTECTED]> Cc: "Group MySQL List" <[EMAIL PROTECTED]> Sent: Friday, June 25, 2004 8:01 AM Subject: Re: ENUM vs TINYINT > Cemal, > > I recall hearing a similar question mentioned in a previous email > thread. In fact, here it is: &

Re: ENUM vs TINYINT

Cemal, I recall hearing a similar question mentioned in a previous email thread. In fact, here it is: http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=c6h60e%2419dd%241%40FreeBSD.csie.NCTU.edu.tw&rnum=1&prev=/groups%3Fq%3D%2522enum%2Bor%2Btinyint%2522%26hl%3Den%26lr%3D%26ie%3DUTF-8%26sel

Re: enum or tinyint?

Harald Fuchs wrote: Even better, in this case you can use BOOL as the column type. Although that's just a synonym of TINYINT, it makes the intended usage clearer. I suppose, except that mysql (4.0.17, anyway) doesn't remember that you used BOOL. mysql> CREATE TABLE bt (flag BOOL); Query OK, 0 r

Re: enum or tinyint?

Arthur Radulescu wrote: What is recommended for a large database to use enum or tinyint? Should I store active inactive or tinyint (1) with 0 and 1? Thanks, Arthur What matters to you, space, speed, or ease of use? A tinyint and an enum with just 2 values both take up one byte, so the space ques

Re: enum version info

Matthew P Ryder <[EMAIL PROTECTED]> wrote: > > Quick question since I can't see to find version information online. > What version was enum first supported under? > It's supported from 3.21.0: http://www.mysql.com/doc/en/News-3.21.0.html -- For technical support contracts, goto http

Re: Enum default values

Hi, > > This usually comes up when people expect an exception to be thrown when > they assign NULL to a NOT NULL column, but this is the same idea. From > the manual : Indeed. One of those weird MySQL things. Is this different on InnoDB? > >

Re: Enum default values

Martijn Tonies wrote: Hi, [snip] If you insert an invalid value into an ENUM (that is, a string not present in the list of allowed values), the empty string is inserted instead as a special error value. This string can be distinguished from a 'normal' empty string by the fact that this string h

Re: Enum default values

Hi, > [snip] > > If you insert an invalid value into an ENUM (that is, a string not > > present in the list of allowed values), the empty string is inserted > > instead as a special error value. This string can be distinguished > from > > a 'normal' empty string by the fact that this string has th

RE: Enum default values

[snip] > If you insert an invalid value into an ENUM (that is, a string not > present in the list of allowed values), the empty string is inserted > instead as a special error value. This string can be distinguished from > a 'normal' empty string by the fact that this string has the numerical > val

Re: Enum default values

> [snip] > Yes, the DEFAULT doesn't apply. However, shouldn't MySQL > raise an exception because '' isn't a valid value for this ENUM > specification? > [/snip] > > >From http://www.mysql.com/doc/en/ENUM.html > > The value may also be the empty string ("") or NULL under certain > circumstances:

RE: Enum default values

[snip] Yes, the DEFAULT doesn't apply. However, shouldn't MySQL raise an exception because '' isn't a valid value for this ENUM specification? [/snip] >From http://www.mysql.com/doc/en/ENUM.html The value may also be the empty string ("") or NULL under certain circumstances: If you insert an in

Re: Enum default values

Hi, > [snip] > insert into property values ('','Book-keeper\'s Cottage','Thu'); > insert into property values ('','Inglenook Barn','Fri'); > insert into property values ('','Maggie\'s House','Fri'); > insert into property values ('','Riverside View',''); > insert into property values ('','The Mans

Re: Enum default values

Jay! Thanks for help with a 'proper' insert. ;-) Yes, the full statement works fine. (I assume this is something like the not setting default values from importing DATA files?) All a bit of a shame, as I'm executing the insert from a PHP script, and was trying to make the routine as generic as

RE: Enum default values

[snip] insert into property values ('','Book-keeper\'s Cottage','Thu'); insert into property values ('','Inglenook Barn','Fri'); insert into property values ('','Maggie\'s House','Fri'); insert into property values ('','Riverside View',''); insert into property values ('','The Manse',''); insert in

Re: enum for bool in the future

Abs wrote: hi i know this has been discussed before, storing bools in the right column type. in the case of enum as: enum("N","Y") or "n","y", etc. it might be easy to read when u're looking at the database table itself, but if u had to communicate this properly to other programmers designing a fro

Re: Enum Columns

Ruben, > I has a column defined as > enum not null (percent,absolute) default value percent (in psuedocode) > and I was able to update a record which filled it either with a NULL or > an empty string. > How does this happen? the value should be either percent or absolute, > or the updat

Re: ENUM and select performance

Alex, This is a specialised question! Unfortunately, in my experience, it comes down to the phrase YMMV (your mileage might vary) and thus requires you to experiment with your data/machine combinations/etc - good job MySQL makes this quite easy! Can I assume that you have done your (manual) homew

Re: ENUM query question

Maybe you should say WHERE category LIKE 'Gambling' - Original Message - From: Jeff Field <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, June 12, 2002 11:42 AM Subject: ENUM query question > I have a quick question regarding queries that involve values in ENUM > columns.

Re: ENUM query question

At 12:42 -0500 6/12/02, Jeff Field wrote: >I have a quick question regarding queries that involve values in ENUM >columns. Here's the scenario: > >I have an ENUM column (category) that can have the values ('Gambling', >'Geographic', 'Medical', 'Sports'). Most of the records have only one of >the

RE: Enum Type and the ANSI SQL Standard

char(1) default to either 0 or 1. I do not believe that SQL93 has a boolean data type nor a standard for implementing it. =C= * * Cal Evans * Journeyman Programmer * Techno-Mage * http://www.calevans.com * -Original Message- From: Michael Ivanyo [mailto:[EMAIL PROTECTED]] Sent: Tuesday

Re: ENUM Default values on NULL

Ruben, I think youare referring to my earlier message, but no matter. You cannot insert NULL into a column you've defined as NOT NULL, with some special exceptions (auto_increment and timestamp, for example). As I said before, if you want a column to get its default value, you leave it out of t

Re: ENUM Default values on NULL

Your example is not the same, it sends only one value to a 2 value table. It definetely does not work if you send NULL INSERT VALUES(NULL) Ruben On 2002.04.12 14:10 Michael Stassen wrote: > > On Fri, 12 Apr 2002, Steve Katen wrote: > > > Ruben, > > > > If you leave it as NOT NULL it should

Re: ENUM Default values on NULL

On Fri, 12 Apr 2002, Steve Katen wrote: > Ruben, > > If you leave it as NOT NULL it should default to NO. "If an ENUM is > declared NOT NULL, the default value is the first element of the list of > allowed values." > > SIDE QUESTION: > Are you doing something like: select * from table where enu

Re: ENUM Default values on NULL

Instead of inserting NULL, leave the column out. INSERT mytable (2nd_col_name) VALUES (NULL); If you don't mention the enum column, it gets the default. Michael On Fri, 12 Apr 2002, Ruben I Safir wrote: > When I send a NULL it's rejected as bad data, which sort of makes sense > > > On 2002.0

Re: ENUM Default values on NULL

Ruben, "If you insert an invalid value into an ENUM (that is, a string not present in the list of allowed values), the empty string is inserted instead as a special error value." it is inserting the value as the first value in the table which is the error value or the index of 0. i would ass

Re: ENUM Default values on NULL

Steve Katen wrote: > Ruben, > > If you leave it as NOT NULL it should default to NO. "If an ENUM is > declared NOT NULL, the default value is the first element of the list > of allowed values." > > SIDE QUESTION: > Are you doing something like: select * from table where enum_colum="NO" > > I

Re: ENUM Default values on NULL

When I send a NULL it's rejected as bad data, which sort of makes sense On 2002.04.12 12:33 Steve Katen wrote: > Ruben, > > If you leave it as NOT NULL it should default to NO. "If an ENUM is > declared NOT NULL, the default value is the first element of the list of > allowed values." > Th

Re: ENUM Default values on NULL

Ruben, If you leave it as NOT NULL it should default to NO. "If an ENUM is declared NOT NULL, the default value is the first element of the list of allowed values." SIDE QUESTION: Are you doing something like: select * from table where enum_colum="NO" If you are running that type of query it

Re: ENUM Default values on NULL

sql > > ENUM can handle your needs. you should be able to just change your syntax > > to: FIELD ENUM('NO','YES') NULL > > > > under that syntax your default value will be NULL. > > > I need it to default to 'NO' not NULL -

Re: ENUM Default values on NULL

> ENUM can handle your needs. you should be able to just change your syntax > to: FIELD ENUM('NO','YES') NULL > > under that syntax your default value will be NULL. We need it to default to 'NO' not NULL sql Ruben - Before p

Re: ENUM Default values on NULL

ENUM can handle your needs. you should be able to just change your syntax to: FIELD ENUM('NO','YES') NULL under that syntax your default value will be NULL. this is from the documentation directly: "If an ENUM is declared NULL, NULL is also a legal value for the column, and the default value

Re: ENUM Default values on NULL

sql > > Hello > > I need a ENUM to default to a value when it gets a NULL. > > Can this be done. Right now is I set the column to > FIELD ENUM('NO','YES') NOT NULL > > It fails to insert when a NULL is entered. > > I want it to default to NO > > > Can I set it to do this? > > Ruben >

Re: ENUM, SET, TINYINT

At 16:53 -0500 4/3/02, Stephen Simons wrote: >I have read the MySQL manual on column types and I >can't find anywhere which is smaller, ENUM, SET, or >TINYINT? > >Could somebody let me know which of those three is the >smaller data type? Go to the manual link at the bottom of the message and type

Re: ENUM -- integers or strings?

PHP will handle your var types for you just fine. If you want to bet sure, so an intval() before you compare. On Fri, 4 Jan 2002 16:18:24 -0500, Erik Price used a few recycled electrons to form: | There's no data in the database yet, so I haven't tested this code. I | don't want to use the mys

Re: ENUM -- integers or strings?

There's no data in the database yet, so I haven't tested this code. I don't want to use the mysql CLI client to input data b/c the data is spread out over a number of tables, rather, I'm writing PHP pages that provide a means to populate the database in an organized way. But until the PHP is

RE: ENUM -- integers or strings?

What happened when you experimented? What were your results? -Original Message- From: Erik Price [mailto:[EMAIL PROTECTED]] Sent: Friday, January 04, 2002 2:03 PM To: [EMAIL PROTECTED] Subject: ENUM -- integers or strings? A quick question -- If I have a table with an ENUM column, and

Re: enum errors - what is the correct syntax?

McGrotty, Charles writes: > I have been bashing my head for a couple of hours now trying to > create an enum field which will hold a true or false (1 or 0) > value, but I always get an error when I try to create the enum > field. An ENUM can only hold strings, so you're probably missing the quo

Re: ENUM NOT NULL without default value

At 2:43 AM +0100 10/1/01, Corin Hartland-Swann wrote: >Hi Monty, > >I've been experimenting with ENUM NOT NULL columns without a default >value. The documentation (section 6.5.3 again) states: > >"If no DEFAULT value is specified for a column, MySQL automatically >assigns one. If the column may ta

Re: Enum

Ann Myhre wrote: >Hi group > >Access has a datatype (I don't remember the name) which is a kind of boleaen; it >represents Yes/No, true or false. When I try to translate that creating a table in >MySQL I try ENUM("N","Y") but I do not get the expected result when using If Y >then...(ASP). Do I

Re: ENUM and large strings

> > > In order to save space in our database, we designed our tables to use > > > enumerated datatypes. For one column, we have about 2,800 > > > different values, > > > each of which is about 30-40 text characters long. Now, all the MySQL > > > documentation I have read states that the limit fo

RE: ENUM and large strings

> > In order to save space in our database, we designed our tables to use > > enumerated datatypes. For one column, we have about 2,800 > > different values, > > each of which is about 30-40 text characters long. Now, all the MySQL > > documentation I have read states that the limit for the num

RE: ENUM and large strings

Perhaps in your case it would be better to use another table and do a join? > Hi! > > In order to save space in our database, we designed our tables to use > enumerated datatypes. For one column, we have about 2,800 > different values, > each of which is about 30-40 text characters long. Now,

Re: ENUM or not ENUM?

I would leave it the way it is, if I were you except for one thing. Change the table name Colours to CarColours. Will make it a little less confusing in the future. - Original Message - From: "îÉËÏÌÉÎ óÅÒÇÅÊ" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, February 20, 2001

Re: ENUM or not ENUM?

check out the "set" type instead of enum. http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#Column_types http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#SET -tcl. On Tue, 20 Feb 2001, [KOI8-R] "îÉËÏÌÉÎ óÅÒÇÅÊ" wrote: > Hello > > I have situatio