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 primarily be used in written
descriptions. Is this a wise or unwise way to design a table column?
Thanks
Depending on the specific sport (and level of competition), there may be
more than one OT period. Do you really want to aggregate all of the OT
stats into just one bucket?
It makes better sense to me to use a TINYINT for storage then for any
values >=5 convert to "OT", "OT2", ... unless it makes no difference
for your purposes which period of extra play you might be in.
This would also allow you to easily query your stats for any rows where
`quarter`>4 to see which games, if any, experienced any OT play at all.
You could do the same with ENUMS but then you would need a longer list
of ENUMS (OT, OT2, OT3, ... ) to cover all the possibilities.
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN
Become certified in MySQL! Visit https://www.mysql.com/certification/
for details.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: ENUM() vs TINYINT
> From: Richard Reina <gatorre...@gmail.com>
>
> 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 used in written
> descriptions. Is this a wise or unwise way to design a table column?
I think it's a wise way to do things.
I use ENUMs a lot, whenever I'm choosing from a fixed set of a relatively small
number of items that will not change frequently (or at all).
One other thing to consider is if this particular set of choices will be used
elsewhere. If so, then consider using a TINYINT index into a different table
that associates those indices (PK) with strings. Otherwise, I see no good
reason to use TINYINT.
You don't have to take insults personally. You can sidestep negative
energy; you can look for the good in others and utilize that good, as different
as it may be, to improve your point of view and to enlarge your perspective. --
Stephen R. Covey
Jan Steinman, EcoReality Co-op
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
ENUM() vs TINYINT
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 used in written
descriptions. Is this a wise or unwise way to design a table column?
Thanks
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 <gatorre...@gmail.com> 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 primarily be used in written
> descriptions. Is this a wise or unwise way to design a table column?
>
> Thanks
>
Re: Possible to get better error handling for invalid enum parameter to stored program?
If you want to keep the enum column, you can set the strict SQL mode:
mysql show variables like 'SQL_MODE';
+---+---+
| Variable_name | Value |
+---+---+
| sql_mode | |
+---+---+
1 row in set (0.00 sec)
mysql create table test2 (test enum('foo','bar'));
Query OK, 0 rows affected (0.01 sec)
mysql insert into test2 values('baz');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql show warnings;
+-+--+---+
| Level | Code | Message |
+-+--+---+
| Warning | 1265 | Data truncated for column 'test' at row 1 |
+-+--+---+
1 row in set (0.00 sec)
mysql set session sql_mode='traditional';
Query OK, 0 rows affected (0.00 sec)
mysql insert into test2 values('bloop');
ERROR 1265 (01000): Data truncated for column 'test' at row 1
mysql select * from test2;
+--+
| test |
+--+
| |
+--+
1 row in set (0.00 sec)
I'm running v5.0.51a; as you can see, the first invalid value was
truncated to '' with a warning, the second caused an error and did
not insert. I don't know what version you're running, perhaps this
does not apply to you. For more info:
http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html
http://dev.mysql.com/doc/refman/5.0/en/enum.html
- steve edberg
At 2:00 PM -0800 3/6/09, David Karr wrote:
Thanks. I thought that was the case, but I wanted to be sure.
On Fri, Mar 6, 2009 at 12:07 PM, Perrin Harkins per...@elem.com wrote:
I'm afraid enums are useless for anything except compressing your
data, since they don't reject bad values. If you want to limit a
field to a set of values, you need to use a lookup table and a foreign
key constraint.
- Perrin
On Fri, Mar 6, 2009 at 1:35 PM, David Karr davidmichaelk...@gmail.com
wrote:
If I define an enum parameter for a stored program, and the calling code
sends an invalid value, they get the less than useful data truncated
error. Is it possible to define the stored program to produce better
error
handling for that kind of error?
This is probably a FAQ, but in general, it appears that error diagnostics
in
stored programs are very primitive. Are there any plans in a roadmap to
improve this?
--
+--- my people are the people of the dessert, ---+
| Steve Edberghttp://pgfsun.ucdavis.edu/ |
| UC Davis Genome Centersbedb...@ucdavis.edu |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+ said t e lawrence, picking up his fork +
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Possible to get better error handling for invalid enum parameter to stored program?
On Sun, Mar 8, 2009 at 3:31 PM, Steve Edberg sbedb...@ucdavis.edu wrote:
If you want to keep the enum column, you can set the strict SQL mode:
mysql show variables like 'SQL_MODE';
+---+---+
| Variable_name | Value |
+---+---+
| sql_mode | |
+---+---+
1 row in set (0.00 sec)
mysql create table test2 (test enum('foo','bar'));
Query OK, 0 rows affected (0.01 sec)
mysql insert into test2 values('baz');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql show warnings;
+-+--+---+
| Level | Code | Message |
+-+--+---+
| Warning | 1265 | Data truncated for column 'test' at row 1 |
+-+--+---+
1 row in set (0.00 sec)
mysql set session sql_mode='traditional';
Query OK, 0 rows affected (0.00 sec)
mysql insert into test2 values('bloop');
ERROR 1265 (01000): Data truncated for column 'test' at row 1
mysql select * from test2;
+--+
| test |
+--+
| |
+--+
1 row in set (0.00 sec)
I'm running v5.0.51a; as you can see, the first invalid value was truncated
to '' with a warning, the second caused an error and did not insert. I don't
know what version you're running, perhaps this does not apply to you. For
more info:
I think you missed the point of my question. I'm currently in strict mode.
I think that's a good idea, in general. The error (not warning) I get when I
submit an invalid enum value is essentially data truncated for column X.
My point is that that error message doesn't give any clue (except for the
column name) what the actual problem is. Another poster pointed out that
there's no resolution to this, outside of not using enum columns, or simply
accepting their limitations.
http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html
http://dev.mysql.com/doc/refman/5.0/en/enum.html
- steve edberg
At 2:00 PM -0800 3/6/09, David Karr wrote:
Thanks. I thought that was the case, but I wanted to be sure.
On Fri, Mar 6, 2009 at 12:07 PM, Perrin Harkins per...@elem.com wrote:
I'm afraid enums are useless for anything except compressing your
data, since they don't reject bad values. If you want to limit a
field to a set of values, you need to use a lookup table and a foreign
key constraint.
- Perrin
On Fri, Mar 6, 2009 at 1:35 PM, David Karr davidmichaelk...@gmail.com
wrote:
If I define an enum parameter for a stored program, and the calling
code
sends an invalid value, they get the less than useful data truncated
error. Is it possible to define the stored program to produce better
error
handling for that kind of error?
This is probably a FAQ, but in general, it appears that error
diagnostics
in
stored programs are very primitive. Are there any plans in a roadmap
to
improve this?
--
+--- my people are the people of the dessert, ---+
| Steve Edberghttp://pgfsun.ucdavis.edu/ |
| UC Davis Genome Centersbedb...@ucdavis.edu |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+ said t e lawrence, picking up his fork +
Possible to get better error handling for invalid enum parameter to stored program?
If I define an enum parameter for a stored program, and the calling code sends an invalid value, they get the less than useful data truncated error. Is it possible to define the stored program to produce better error handling for that kind of error? This is probably a FAQ, but in general, it appears that error diagnostics in stored programs are very primitive. Are there any plans in a roadmap to improve this?
Re: Possible to get better error handling for invalid enum parameter to stored program?
I'm afraid enums are useless for anything except compressing your data, since they don't reject bad values. If you want to limit a field to a set of values, you need to use a lookup table and a foreign key constraint. - Perrin On Fri, Mar 6, 2009 at 1:35 PM, David Karr davidmichaelk...@gmail.com wrote: If I define an enum parameter for a stored program, and the calling code sends an invalid value, they get the less than useful data truncated error. Is it possible to define the stored program to produce better error handling for that kind of error? This is probably a FAQ, but in general, it appears that error diagnostics in stored programs are very primitive. Are there any plans in a roadmap to improve this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Possible to get better error handling for invalid enum parameter to stored program?
Thanks. I thought that was the case, but I wanted to be sure. On Fri, Mar 6, 2009 at 12:07 PM, Perrin Harkins per...@elem.com wrote: I'm afraid enums are useless for anything except compressing your data, since they don't reject bad values. If you want to limit a field to a set of values, you need to use a lookup table and a foreign key constraint. - Perrin On Fri, Mar 6, 2009 at 1:35 PM, David Karr davidmichaelk...@gmail.com wrote: If I define an enum parameter for a stored program, and the calling code sends an invalid value, they get the less than useful data truncated error. Is it possible to define the stored program to produce better error handling for that kind of error? This is probably a FAQ, but in general, it appears that error diagnostics in stored programs are very primitive. Are there any plans in a roadmap to improve this?
Re: Relational Databasing on busy webserver (Benchmark of Enum?!)
I figured that was what you meant... I guess my table didn't work (see above message...don't ya' love plaintext :-O)... Has anyone ever tried to benchmark the difference between utilizing ENUMs vs. traditional relational databasing? I would think ENUM is ideal for items I specified at the beginning of this thread, items I would think would be part of MANY (if not MOST) databases (state, country, gender, industry, occupation, referredFrom, ethnicity, position)... In my case, it would allow me to eliminate 15+ tables... I'm just wondering why database ENUMS aren't used more often... (what's the catch) Olexandr Melnyk [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] On Tue, Sep 23, 2008 at 6:13 PM, Olexandr Melnyk [EMAIL PROTECTED] wrote: Plus, if the same query is run very often and table is almost static, chances are high that the result will be in query cache. Just realized that I haven't mentioned that this sentence is related to storing states in the database, rather than in the application layer. -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Relational Databasing on busy webserver (Benchmark of Enum?!)
On 09/23/2008 02:42 PM, Ben A.H. wrote: I figured that was what you meant... I guess my table didn't work (see above message...don't ya' love plaintext :-O)... Has anyone ever tried to benchmark the difference between utilizing ENUMs vs. traditional relational databasing? I would think ENUM is ideal for items I specified at the beginning of this thread, items I would think would be part of MANY (if not MOST) databases (state, country, gender, industry, occupation, referredFrom, ethnicity, position)... In my case, it would allow me to eliminate 15+ tables... I'm just wondering why database ENUMS aren't used more often... (what's the catch) Just thought I'd jump in with some terms here: When you're saying 'traditional relational databasing' is kind of misleading. You're probably still relational in some sense of the term even using a ton of ENUMs. What you're talking about is fully normalized form. Take a look here: http://en.wikipedia.org/wiki/Database_normalization ..to answer your question, normalization isn't done for speed. In fact, I'd hazard a guess that fully normalized databases are almost always slower than if the designed makes some shortcuts. What they provide is a fully structured way to organize your data. If you're just storing data, and you know there's only a limited number of ways you're going to pull data out of the set, then I'd make as many shortcuts as I could to provide speed. If you are using the dataset for analysis, and you're not sure how the users are going to extract data, then you may want to go further down the normalization road. As with anything normalization can reach a point of dubious effect, but it serves a very important purpose. By normalizing your data, and not using things like ENUMs, you're maintaining an accurate data structure which then can be arbitrarily used. But yeah, it's not necessarily fast, probably the opposite. Hope that helps. -Micah -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SET vs. ENUM
Hello Jerry and Martijn sets contains an iterator so you can iterate thru the objects also supports the contains method set.contains(new String(foo)) http://www.docjar.com/docs/api/java/util/Set.html Enums must use the exact index and are generally use for fixed constant Array http://java.sun.com/j2se/1.5.0/docs/guide/language/enums.html I fail to see what this has to do with MySQL? Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SET vs. ENUM
Other than the fact that an ENUM can have many more values than a SET, is there any particular reason to choose one over the other? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 http://www.the-infoshop.com www.the-infoshop.com http://www.giiexpress.com www.giiexpress.com www.etudes-marche.com
Re: SET vs. ENUM
Jerry, Other than the fact that an ENUM can have many more values than a SET, is there any particular reason to choose one over the other? Well, they are -different things- ... SETs shouldn't be used at all, IMO. ENUM can be somewhat useful, I guess. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SET vs. ENUM
-Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Thursday, July 31, 2008 9:51 AM To: mysql@lists.mysql.com Subject: Re: SET vs. ENUM Jerry, Other than the fact that an ENUM can have many more values than a SET, is there any particular reason to choose one over the other? Well, they are -different things- ... SETs shouldn't be used at all, IMO. [JS] Why? ENUM can be somewhat useful, I guess. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SET vs. ENUM
Jerry, Other than the fact that an ENUM can have many more values than a SET, is there any particular reason to choose one over the other? Well, they are -different things- ... SETs shouldn't be used at all, IMO. [JS] Why? It goes against normalizing your data, that's why. You cannot query them properly, that's why (just check the archives of this list). You cannot add a possible value (same goes for ENUM) without a DDL statement, so data is intertwined with metadata, which is bad. The metadata is not portable to other systems (neither is ENUM). ENUM can be somewhat useful, I guess. Need more reasons? :-) Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SET vs. ENUM
On Thu, Jul 31, 2008 at 9:46 AM, Jerry Schwartz [EMAIL PROTECTED] wrote: Other than the fact that an ENUM can have many more values than a SET, is there any particular reason to choose one over the other? The only use for ENUM is to make your data smaller. It offers no protection against illegal values and can't be updated without copying the table. If you want to constrain values, a better approach is to make your tables InnoDB and use a lookup table with a foreign key constraint. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SET vs. ENUM
On Thu, 2008-07-31 at 11:24 -0400, Perrin Harkins wrote: On Thu, Jul 31, 2008 at 9:46 AM, Jerry Schwartz [EMAIL PROTECTED] wrote: Other than the fact that an ENUM can have many more values than a SET, is there any particular reason to choose one over the other? The only use for ENUM is to make your data smaller. It offers no protection against illegal values and can't be updated without copying the table. If you want to constrain values, a better approach is to make your tables InnoDB and use a lookup table with a foreign key constraint. - Perrin I don't see how that can be; with ENUM the DB has to set aside enough bytes for the longest identifier. The only advantage of ENUM is that the data is in the same table; you don't have to do an extra join. It can save you some processing time. -- Just my 0.0002 million dollars worth, Shawn Where there's duct tape, there's hope. Perl is the duct tape of the Internet. Hassan Schroeder, Sun's first webmaster -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SET vs. ENUM
On Thu, Jul 31, 2008 at 11:38 AM, Mr. Shawn H. Corey [EMAIL PROTECTED] wrote: I don't see how that can be; with ENUM the DB has to set aside enough bytes for the longest identifier. ENUMs are stored as integers. The only advantage of ENUM is that the data is in the same table; you don't have to do an extra join. You don't have to join in order to use a lookup table. You can store the actual values in the column (unlike ENUM). - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SET vs. ENUM
-Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Thursday, July 31, 2008 10:32 AM To: mysql@lists.mysql.com Subject: Re: SET vs. ENUM Jerry, Other than the fact that an ENUM can have many more values than a SET, is there any particular reason to choose one over the other? Well, they are -different things- ... SETs shouldn't be used at all, IMO. [JS] Why? It goes against normalizing your data, that's why. You cannot query them properly, that's why (just check the archives of this list). You cannot add a possible value (same goes for ENUM) without a DDL statement, so data is intertwined with metadata, which is bad. The metadata is not portable to other systems (neither is ENUM). ENUM can be somewhat useful, I guess. Need more reasons? :-) [JS] No, you've made several good points. My reasoning is that inexperienced folks using this table through MS Access don't generally understand enough about database design to create the necessary JOINs to do it right. Perhaps the trade-off is a bad one. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SET vs. ENUM
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Perrin Harkins Sent: Thursday, July 31, 2008 11:25 AM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: Re: SET vs. ENUM On Thu, Jul 31, 2008 at 9:46 AM, Jerry Schwartz [EMAIL PROTECTED] wrote: Other than the fact that an ENUM can have many more values than a SET, is there any particular reason to choose one over the other? The only use for ENUM is to make your data smaller. It offers no protection against illegal values and can't be updated without copying the table. If you want to constrain values, a better approach is to make your tables InnoDB and use a lookup table with a foreign key constraint. [JS] Too late, I inherited this. There are a LOT of places where foreign key restraints (and cascading deletes) would save a lot of programming. I do, however, make use of full-text indexing. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SET vs. ENUM
Jerry Schwartz wrote: [JS] No, you've made several good points. My reasoning is that inexperienced folks using this table through MS Access don't generally understand enough about database design to create the necessary JOINs to do it right. Perhaps the trade-off is a bad one. My thought is you should develop an application that will give your users the information they need with out direct access to the DB. My thought is, if a user doesn't have a solid understanding of at least 1st and 2nd normal form, and basic joins, they should not be given direct access to the DB. Doing so would be kind of like giving a 16 year old kid the keys to a 200mph race car and say have fun. Sooner or later something bad is going to happen. Just my opinion. -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Ham Radio Repeater Database. http://hrrdb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SET vs. ENUM
On Thu, 2008-07-31 at 11:47 -0500, Chris W wrote: My thought is you should develop an application that will give your users the information they need with out direct access to the DB. My thought is, if a user doesn't have a solid understanding of at least 1st and 2nd normal form, and basic joins, they should not be given direct access to the DB. Doing so would be kind of like giving a 16 year old kid the keys to a 200mph race car and say have fun. Sooner or later something bad is going to happen. Just my opinion. Actually, it's scarier to give access to people who know what they're doing. They're the ones who would know how to sabotage it. Access should only be granted to those who need it to do their jobs. Everyone else should be restricted to using a user interface with predefined pathways. -- Just my 0.0002 million dollars worth, Shawn Where there's duct tape, there's hope. Perl is the duct tape of the Internet. Hassan Schroeder, Sun's first webmaster -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SET vs. ENUM
Mr. Shawn H. Corey wrote: Actually, it's scarier to give access to people who know what they're doing. They're the ones who would know how to sabotage it. Access should only be granted to those who need it to do their jobs. Everyone else should be restricted to using a user interface with predefined pathways. Can't really argue with that. -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Ham Radio Repeater Database. http://hrrdb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
something like ENUM - but it's not ENUM
hi,
I wonder if mysql has something like ENUM() but instead ONLY ONE it
could be selected ANY COMBINATION of these offered solutioins?
If column is ENUM('a','b','c','d') I can select 'a' or 'b' or 'c' or
'd'. Clear.
Tough, I need SOMETHING('a','b','c','d') that I can select 'a' and 'b',
or 'b', 'c' and 'd', or all of them (but must at least one)? Does exists
something like this in mysql?
thanks,
-afan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: something like ENUM - but it's not ENUM
yes. just found it.
it's funny, I was looking for it last half hour and the second after I
sent the email - I found it.
:)
yes, yes... I know. RTFM
:D
thanks.
-afan
sol beach wrote:
SET Column type?
On Dec 6, 2007 2:01 PM, Afan Pasalic [EMAIL PROTECTED] wrote:
hi,
I wonder if mysql has something like ENUM() but instead ONLY ONE it
could be selected ANY COMBINATION of these offered solutioins?
If column is ENUM('a','b','c','d') I can select 'a' or 'b' or 'c' or
'd'. Clear.
Tough, I need SOMETHING('a','b','c','d') that I can select 'a' and 'b',
or 'b', 'c' and 'd', or all of them (but must at least one)? Does exists
something like this in mysql?
thanks,
-afan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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) letters, NULL is a keyword denoting the
unknown value.
I suspect MySQL receives a character string of 'N', 'U', 'L', 'L' (in
one string) and truncates this to the first character, because this
matches the column definition.
What I am doing is moving data from one table to another with a python
script so I have to assign 'NULL' to the variable in the insert string (at
least to my knowledge) because python retrieves None (type type
'NoneType') when querying a NULL value.
I cannot comment on that.
HTH,
Jörg
--
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Enum issue
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?
What I am doing is moving data from one table to another with a python
script so I have to assign 'NULL' to the variable in the insert string (at
least to my knowledge) because python retrieves None (type type
'NoneType') when querying a NULL value.
Help is appreciated.
Olaf
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Enum issue
Hi,
Try with,
, `consent` enum ('','Y','N','P') ,
.mysql desc table;
+---+--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-+---+
.
| consent | enum('','Y','N','P') | YES | | NULL| |
+---+--+--+-+-+---+
Thanks
ViSolve DB Team
- Original Message -
From: Olaf Stein [EMAIL PROTECTED]
To: MySql mysql@lists.mysql.com
Sent: Wednesday, January 10, 2007 3:44 AM
Subject: Enum issue
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?
What I am doing is moving data from one table to another with a python
script so I have to assign 'NULL' to the variable in the insert string (at
least to my knowledge) because python retrieves None (type type
'NoneType') when querying a NULL value.
Help is appreciated.
Olaf
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
jdbc question regarding enum
disclaimer: i know nothing about java or jdbc. a co-worker is trying to access a database i've set up using jdbc. he says that my enum column is always returning an integer value instead of the string. obviously this is less than desirable. does anybody have any advice i could give him on where to look or something to change? -jsd- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: too many enum values?
I think so... Tanner Postert [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] how many is too many? i have a field with 21 possible values. each of the values are only 2 or 3 letter strings, but that seems like a lot, would it be faster/more efficient to put them in a separate table and just join? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: too many enum values?
how many is too many? i have a field with 21 possible values. each of the values are only 2 or 3 letter strings, but that seems like a lot, would it be faster/more efficient to put them in a separate table and just join? What would you put in the separate table then? Just these 2-3 letter strings and use the value as the PK? You should ask yourself the following -- - do these values change depending on the customer? If so: table. - can customers add possible values for this column? If so: table - is there some kind of description for the possible values? If so: table - is this an internally used value/code? If so: enum would be fine - would values only be added during a database upgrade? If so: enum would be fine All in my opinion, of course. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
too many enum values?
how many is too many? i have a field with 21 possible values. each of the values are only 2 or 3 letter strings, but that seems like a lot, would it be faster/more efficient to put them in a separate table and just join?
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 != to the
other 5 possible enum values. the query returns in 0.00 sec.
there is an index on that field... shouldn't the = 'true' query be faster?
i just dont understand.
Are there more rows returned for 'true' than for the other values?
Have you tried using something other than a reserved word for 'true'?
What other values for file_complete could there be than true and false?
Why not define e.g. upload_status enum ('working', , 'complete').
(BTW you don't need those (parentheses) in your query.)
-J
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 results are happening elsewhere
where i don't use it. and since it's a string, it wouldn't know that its a
reserved word.
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.
On 7/4/06, John Hicks [EMAIL PROTECTED] wrote:
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 != to
the
other 5 possible enum values. the query returns in 0.00 sec.
there is an index on that field... shouldn't the = 'true' query be
faster?
i just dont understand.
Are there more rows returned for 'true' than for the other values?
Have you tried using something other than a reserved word for 'true'?
What other values for file_complete could there be than true and false?
Why not define e.g. upload_status enum ('working', , 'complete').
(BTW you don't need those (parentheses) in your query.)
-J
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 occurs more than 50% of the time. As a general rule, an index on such a column will NOT be useful for selecting rows having the often-occurring value. Such an index is only useful if you *only* need to select the non-occurring values. What you CAN, perhaps do, though, is create a multi-column index instead, so that MySQL doesn't need to scan all rows and order them for you. Your query was: select *, id as vid, user_id as uid from video where (file_complete = 'true') order by undt desc limit 0,10; Creating an index on (file_complete, undt) should work nicely: ALTER TABLE video ADD INDEX (file_complete, undt); Regards, Jeremy -- high performance mysql consulting www.provenscaling.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
enum query results strange.
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 != to the other 5 possible enum values. the query returns in 0.00 sec. there is an index on that field... shouldn't the = 'true' query be faster? i just dont understand.
Add a new value in an ENUM by manipulate .frm
Hi,
I need to add another value in an enum so ALTER TABLE table1 MODIFY
Status ENUM('a','b','c') where 'c' is the new value. My problem is
that the tables are VERY big and it would take days to alter them all.
So, i figure the values in enum:s are in the .frm file so why not change
it and leave the rest. I created a new empty table and altered it and
then copied the new .frm file over the old one.
... and it does seem to work! Select, update works fine and I can use
the new value.
My question is if its OK? Has it been done before? Do I miss anything here?
Regards
Jorgen
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Add a new value in an ENUM by manipulate .frm
Jörgen Winqvist schrieb:
Hi,
I need to add another value in an enum so ALTER TABLE table1 MODIFY
Status ENUM('a','b','c') where 'c' is the new value. My problem is
that the tables are VERY big and it would take days to alter them all.
Tried it?
I also use big tables but adding a field takes a few seconds.
Well it does take long if there is a key on the field.
That would rise the querytime.
So, i figure the values in enum:s are in the .frm file so why not change
it and leave the rest. I created a new empty table and altered it and
then copied the new .frm file over the old one.
... and it does seem to work! Select, update works fine and I can use
the new value.
My question is if its OK? Has it been done before? Do I miss anything here?
Probably the indizes might not work properly anymore.
But i am not so much into MySQL that i could tell you how MySQL works
behind ALTER TABLE.
Barry
--
Smileys rule (cX.x)C --o(^_^o)
Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Add a new value in an ENUM by manipulate .frm
Barry wrote:
Jörgen Winqvist schrieb:
Hi,
I need to add another value in an enum so ALTER TABLE table1 MODIFY
Status ENUM('a','b','c') where 'c' is the new value. My problem is
that the tables are VERY big and it would take days to alter them all.
Tried it?
I also use big tables but adding a field takes a few seconds.
Well it does take long if there is a key on the field.
That would rise the querytime.
Yes I have tried it and it takes hours on a table 4 Gb data and 4 Gb
index with apx 50 milj rows and i have 15 of them. The enum field is not
in any indexes.
So, i figure the values in enum:s are in the .frm file so why not
change it and leave the rest. I created a new empty table and altered
it and then copied the new .frm file over the old one.
... and it does seem to work! Select, update works fine and I can use
the new value.
My question is if its OK? Has it been done before? Do I miss anything
here?
Probably the indizes might not work properly anymore.
But i am not so much into MySQL that i could tell you how MySQL works
behind ALTER TABLE.
Barry
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: alter only an enum label
On 6/16/05, Gabriel B. [EMAIL PROTECTED] wrote:
If i have a table with about 800M records. and one of the fields is a
enum(a, b, c) and i want to change it to enum(a,b,x)
My understanding (such as it is) is that the best way to do this is to add a
second column with enum('a','b','x') and set the value using the integer
value of the first column as follows. (adding 0 casts enum as integer)
ALTER TABLE table ADD new_column enum('a','b','x');
UPDATE table SET new_column = old_column + 0;
ALTER TABLE table DROP old_column;
Execution time is dependent on the speed of the update, but it would need to
read every row.
alter only an enum label
If i have a table with about 800M records. and one of the fields is a enum(a, b, c) and i want to change it to enum(a,b,x) will it fall into some optimization and be instant? and what if previously i've never used the c value? isn't there any optimization for that? ...leaving blank labels on a enum? or another command to add new labels to a enum? thanks, Gabriel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: alter only an enum label
If you have c values in the table currently you can just do an
ALTER TABLE tablename CHANGE columnname columnname ENUM('a','b','c','x')
DEFAULT a NOT NULL
then
UPDATE tablename SET columname = 'x' WHERE columname = 'c'
Then
ALTER TABLE tablename CHANGE columnname columnname ENUM('a','b','x')
DEFAULT a NOT NULL
-Original Message-
From: Gabriel B. [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 16, 2005 12:54 PM
To: LISTA mysql
Subject: alter only an enum label
If i have a table with about 800M records. and one of the fields is a
enum(a, b, c) and i want to change it to enum(a,b,x) will
it fall into some optimization and be instant?
and what if previously i've never used the c value? isn't there any
optimization for that? ...leaving blank labels on a enum? or another
command to add new labels to a enum?
thanks,
Gabriel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: alter only an enum label
hum... clever. i liked that solution.
but do have experience on how long it will take with milions of records?
all records havin only a int(11) as unique key and the enum field..
suposing now i have enum(a, b) only, and did a ALTER TABLE
tablename CHANGE columnname columnname ENUM('a','b','c');
thanks,
Gabriel
2005/6/16, Gordon Bruce [EMAIL PROTECTED]:
If you have c values in the table currently you can just do an
ALTER TABLE tablename CHANGE columnname columnname ENUM('a','b','c','x')
DEFAULT a NOT NULL
then
UPDATE tablename SET columname = 'x' WHERE columname = 'c'
Then
ALTER TABLE tablename CHANGE columnname columnname ENUM('a','b','x')
DEFAULT a NOT NULL
-Original Message-
From: Gabriel B. [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 16, 2005 12:54 PM
To: LISTA mysql
Subject: alter only an enum label
If i have a table with about 800M records. and one of the fields is a
enum(a, b, c) and i want to change it to enum(a,b,x) will
it fall into some optimization and be instant?
and what if previously i've never used the c value? isn't there any
optimization for that? ...leaving blank labels on a enum? or another
command to add new labels to a enum?
thanks,
Gabriel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: alter only an enum label
The ALTER TABLE is going to copy the entire table when it executes the
ALTER TABLE so it will take some time. Depends on your server, diaks,
table type etc..
One alternative might be to do a
SELECT a, enumcolumn INTO OUTFILE 'x' FROM tablename;
TRUNCATE tablename;
ALTER TABLE tablename CHANGE columnname columnname ENUM('a','b','c')
DEFAULT a NOT NULL;
LOAD DATA INFILE 'x' INTO TABLE tablename;
I know this seems obtuse, but load data infile and select into outfile
seem to run very fast and for what ever reason may just be faster than
the ALTER TABLE on the fully populated table.
-Original Message-
From: Gabriel B. [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 16, 2005 1:18 PM
To: mysql@lists.mysql.com
Subject: Re: alter only an enum label
hum... clever. i liked that solution.
but do have experience on how long it will take with milions of records?
all records havin only a int(11) as unique key and the enum field..
suposing now i have enum(a, b) only, and did a ALTER TABLE
tablename CHANGE columnname columnname ENUM('a','b','c');
thanks,
Gabriel
2005/6/16, Gordon Bruce [EMAIL PROTECTED]:
If you have c values in the table currently you can just do an
ALTER TABLE tablename CHANGE columnname columnname
ENUM('a','b','c','x')
DEFAULT a NOT NULL
then
UPDATE tablename SET columname = 'x' WHERE columname = 'c'
Then
ALTER TABLE tablename CHANGE columnname columnname ENUM('a','b','x')
DEFAULT a NOT NULL
-Original Message-
From: Gabriel B. [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 16, 2005 12:54 PM
To: LISTA mysql
Subject: alter only an enum label
If i have a table with about 800M records. and one of the fields is a
enum(a, b, c) and i want to change it to enum(a,b,x) will
it fall into some optimization and be instant?
and what if previously i've never used the c value? isn't there any
optimization for that? ...leaving blank labels on a enum? or another
command to add new labels to a enum?
thanks,
Gabriel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY and ENUM -- not alphabetical
I would say this is not a bug. You declared an enum for the column. So
therefore it sorts in enum order. Makes perfect sense. To me MySql is
working correctly. If it did not sort an enum in the order declared for
the enum then i would be annoyed. Enums are not strings.
Declare the column as varchar if you wish to sort _alphabetically_.
On Wed, 08 Jun 2005 23:38:18 +0100, Daevid Vincent [EMAIL PROTECTED]
wrote:
Please tell me there is a way to fix this bug in mysql Ver 12.22
Distrib
4.0.18, for pc-linux-gnu (i686)
I have a column defined like so:
Type
enum('Schedule','Report','Admin','Search','General','License','Access')
If I SELECT, and ORDER BY Type, it is ordering in the order defined by
the
the ENUM, not _alphabetically_ as a sane person would expect. UGH!
Please tell me there is a fix or work around.
--
alex
tel
02380 48 8273
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY and ENUM -- not alphabetical
Or put the members alphabetically in the enum definition in the first
place ...
--
felix
On 09/06/2005, Eric Bergen wrote:
It's not a bug at all. You just hit one of the features of enum :)
If you want to order alphabetically as you describe cast the enum
name to a string like this select col from t order by concat(my_enum);
-Eric
Daevid Vincent wrote:
Please tell me there is a way to fix this bug in mysql Ver 12.22
Distrib 4.0.18, for pc-linux-gnu (i686)
I have a column defined like so:
Type
enum('Schedule','Report','Admin','Search','General','License','Acces
s')
If I SELECT, and ORDER BY Type, it is ordering in the order defined
by the the ENUM, not alphabetically as a sane person would expect.
UGH!
Please tell me there is a fix or work around.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY and ENUM -- not alphabetical
Or put the members alphabetically in the enum definition in the first
place ...
Better yet - drop the ENUM al together :-)
Use a lookup table.
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL
Server
Upscene Productions
http://www.upscene.com
--
felix
On 09/06/2005, Eric Bergen wrote:
It's not a bug at all. You just hit one of the features of enum :)
If you want to order alphabetically as you describe cast the enum
name to a string like this select col from t order by concat(my_enum);
-Eric
Daevid Vincent wrote:
Please tell me there is a way to fix this bug in mysql Ver 12.22
Distrib 4.0.18, for pc-linux-gnu (i686)
I have a column defined like so:
Type
enum('Schedule','Report','Admin','Search','General','License','Acces
s')
If I SELECT, and ORDER BY Type, it is ordering in the order defined
by the the ENUM, not alphabetically as a sane person would expect.
UGH!
Please tell me there is a fix or work around.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ORDER BY and ENUM -- not alphabetical
Please tell me there is a way to fix this bug in mysql Ver 12.22 Distrib
4.0.18, for pc-linux-gnu (i686)
I have a column defined like so:
Type
enum('Schedule','Report','Admin','Search','General','License','Access')
If I SELECT, and ORDER BY Type, it is ordering in the order defined by the
the ENUM, not _alphabetically_ as a sane person would expect. UGH!
Please tell me there is a fix or work around.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY and ENUM -- not alphabetical
It's not a bug at all. You just hit one of the features of enum :)
If you want to order alphabetically as you describe cast the enum name
to a string like this
select col from t order by concat(my_enum);
-Eric
Daevid Vincent wrote:
Please tell me there is a way to fix this bug in mysql Ver 12.22 Distrib
4.0.18, for pc-linux-gnu (i686)
I have a column defined like so:
Type
enum('Schedule','Report','Admin','Search','General','License','Access')
If I SELECT, and ORDER BY Type, it is ordering in the order defined by the
the ENUM, not _alphabetically_ as a sane person would expect. UGH!
Please tell me there is a fix or work around.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Add an ENUM Column
Hi, Is it possible to add an ENUM column to a table after it has been created? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Add an ENUM Column
[snip] Is it possible to add an ENUM column to a table after it has been created? [/snip] Yes... http://dev.mysql.com/doc/mysql/en/alter-table.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
enum TRUE/FALSE
I'm trying to figure out how to make Active's null as FALSE and '1' as TRUE,
in enum point of view
Can anyone help me out here, trying to learn enum's phenomenon? I'm not sure
I understood document quite clear -- as of yet :(
mysql desc Associate;
+--+--+--+-+-++
| Field| Type | Null | Key | Default | Extra |
+--+--+--+-+-++
| ID | int(11) | | PRI | NULL| auto_increment |
| LocationID | int(11) | YES | | NULL||
| NTLogon | varchar(8) | YES | | NULL||
| DomainID | int(11) | YES | | NULL||
| LastName | varchar(30) | YES | | NULL||
| FirstName| varchar(30) | YES | | NULL||
| Shift| int(11) | YES | | NULL||
| QADE | enum('','1') | YES | | NULL||
| DataEntry| enum('','1') | YES | | NULL||
| QAMR | enum('','1') | YES | | NULL||
| MailRoom | enum('','1') | YES | | NULL||
| QAT | enum('','1') | YES | | NULL||
| Taping | enum('','1') | YES | | NULL||
| QAF | enum('','1') | YES | | NULL||
| Filming | enum('','1') | YES | | NULL||
| CustomerContact | enum('','1') | YES | | NULL||
| Trainee | enum('','1') | YES | | NULL||
| Active | enum('','1') | YES | | NULL||
| Creator | varchar(8) | YES | | NULL||
| NewAssociateDate | date | YES | | NULL||
+--+--+--+-+-++
20 rows in set (0.00 sec)
mysql select count(*) from Associate where Active=FALSE;
+--+
| count(*) |
+--+
|0 |
+--+
1 row in set (0.01 sec)
mysql select count(*) from Associate where Active=TRUE;
+--+
| count(*) |
+--+
| 2611 |
+--+
1 row in set (0.01 sec)
mysql select count(*) from Associate;
+--+
| count(*) |
+--+
| 3947 |
+--+
1 row in set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: enum TRUE/FALSE
Made changes, now the problem has reversed:
mysql select count(*) from Associate where Active=FALSE;
+--+
| count(*) |
+--+
| 2611 |
+--+
1 row in set (0.01 sec)
mysql select count(*) from Associate where Active=TRUE;
+--+
| count(*) |
+--+
|0 |
+--+
1 row in set (0.01 sec)
mysql select count(*) from Associate where Active='1';
+--+
| count(*) |
+--+
| 1336 |
+--+
1 row in set (0.02 sec)
mysql desc Associate;
+--+--+--+-++---
-+
| Field| Type | Null | Key | Default| Extra |
+--+--+--+-++---
-+
| ID | int(11) | | PRI | NULL |
auto_increment |
| LocationID | int(11) | YES | | NULL | |
| NTLogon | varchar(8) | YES | | NULL | |
| DomainID | int(11) | YES | | NULL | |
| LastName | varchar(30) | | || |
| FirstName| varchar(30) | | || |
| Shift| int(11) | | | 1 | |
| QADE | enum('null','1') | YES | | NULL | |
| DataEntry| enum('null','1') | YES | | NULL | |
| QAMR | enum('null','1') | YES | | NULL | |
| MailRoom | enum('null','1') | YES | | NULL | |
| QAT | enum('null','1') | YES | | NULL | |
| Taping | enum('null','1') | YES | | NULL | |
| QAF | enum('null','1') | YES | | NULL | |
| Filming | enum('null','1') | YES | | NULL | |
| CustomerContact | enum('null','1') | YES | | NULL | |
| Trainee | enum('null','1') | YES | | null | |
| Active | enum('null','1') | YES | | NULL | |
| Creator | varchar(8) | | || |
| NewAssociateDate | date | | | -00-00 | |
+--+--+--+-++---
-+
-Original Message-
From: Jay Blanchard [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 10, 2004 9:35 AM
To: Scott Hamm; 'Mysql ' (E-mail)
Subject: 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://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 NULL. If an ENUM column
is declared NOT NULL, its default value is the first element of the list
of allowed values.
If you simply want a column to hold boolean data I prefer to use
tiyint(1) unsigned not null
In my application I then treat 0 as False and 1 (or any other number) as
True
I do not believe you can make mysql evaluate NULL to be False, mysql
will evaluate 1 = True and 0 = False,
TRUE And FALSE are simply aliases for 1 and 0
you have two options
1) You could simply update your table
alter table `Associate` modify `Active` enum('0','1');
(or 1 then 0 if you want true to be the default)
then
update `Associate` set `Active` = 0 where isNull(Active);
2) modify your queries
select count(*) from Associate where Active!=1 OR isNull(Active);
select count(*) from Associate where Active=1;
Chris
-Original Message-
From: Scott Hamm [mailto:[EMAIL PROTECTED]
Sent: 10 November 2004 14:25
To: 'Mysql ' (E-mail)
Subject: enum TRUE/FALSE
I'm trying to figure out how to make Active's null as FALSE and '1' as
TRUE, in enum point of view
Can anyone help me out here, trying to learn enum's phenomenon? I'm not
sure I understood document quite clear -- as of yet :(
mysql desc Associate;
+--+--+--+-+-+--
--+
| Field| Type | Null | Key | Default | Extra
|
+--+--+--+-+-+--
--+
| ID | int(11) | | PRI | NULL|
auto_increment |
| LocationID | int(11) | YES | | NULL|
|
| NTLogon | varchar(8) | YES | | NULL|
|
| DomainID | int(11) | YES | | NULL|
|
| LastName | varchar(30) | YES | | NULL|
|
| FirstName| varchar(30) | YES | | NULL|
|
| Shift| int(11) | YES | | NULL|
|
| QADE | enum('','1') | YES | | NULL|
|
| DataEntry| enum('','1') | YES | | NULL|
|
| QAMR | enum('','1') | YES | | NULL|
|
| MailRoom | enum('','1') | YES | | NULL|
|
| QAT | enum('','1') | YES | | NULL|
|
| Taping | enum('','1') | YES | | NULL|
|
| QAF | enum('','1') | YES | | NULL|
|
| Filming | enum('','1') | YES | | NULL|
|
| CustomerContact | enum('','1') | YES | | NULL|
|
| Trainee | enum('','1') | YES | | NULL|
|
| Active | enum('','1') | YES | | NULL|
|
| Creator | varchar(8) | YES | | NULL|
|
| NewAssociateDate | date | YES | | NULL|
|
+--+--+--+-+-+--
--+
20 rows in set (0.00 sec)
mysql select count(*) from Associate where Active=FALSE;
+--+
| count(*) |
+--+
|0 |
+--+
1 row in set (0.01 sec)
mysql select count(*) from Associate where Active=TRUE;
+--+
| count(*) |
+--+
| 2611 |
+--+
1 row in set (0.01 sec)
mysql select count(*) from Associate;
+--+
| count(*) |
+--+
| 3947 |
+--+
1 row in set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 three values (like: true, false, don't know) then make 3 enum
values. But, regardless of the contents of your ENUM list, I would still
make an ENUM field as NOT NULL and would set its default value to one
of its enumerated values. That way, the field should only hold what I said
it could hold.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Scott Hamm [EMAIL PROTECTED] wrote on 11/10/2004 09:24:33 AM:
I'm trying to figure out how to make Active's null as FALSE and '1' as
TRUE,
in enum point of view
Can anyone help me out here, trying to learn enum's phenomenon? I'm not
sure
I understood document quite clear -- as of yet :(
mysql desc Associate;
+--+--+--+-+-++
| Field| Type | Null | Key | Default | Extra |
+--+--+--+-+-++
| ID | int(11) | | PRI | NULL|
auto_increment |
| LocationID | int(11) | YES | | NULL| |
| NTLogon | varchar(8) | YES | | NULL| |
| DomainID | int(11) | YES | | NULL| |
| LastName | varchar(30) | YES | | NULL| |
| FirstName| varchar(30) | YES | | NULL| |
| Shift| int(11) | YES | | NULL| |
| QADE | enum('','1') | YES | | NULL| |
| DataEntry| enum('','1') | YES | | NULL| |
| QAMR | enum('','1') | YES | | NULL| |
| MailRoom | enum('','1') | YES | | NULL| |
| QAT | enum('','1') | YES | | NULL| |
| Taping | enum('','1') | YES | | NULL| |
| QAF | enum('','1') | YES | | NULL| |
| Filming | enum('','1') | YES | | NULL| |
| CustomerContact | enum('','1') | YES | | NULL| |
| Trainee | enum('','1') | YES | | NULL| |
| Active | enum('','1') | YES | | NULL| |
| Creator | varchar(8) | YES | | NULL| |
| NewAssociateDate | date | YES | | NULL| |
+--+--+--+-+-++
20 rows in set (0.00 sec)
mysql select count(*) from Associate where Active=FALSE;
+--+
| count(*) |
+--+
|0 |
+--+
1 row in set (0.01 sec)
mysql select count(*) from Associate where Active=TRUE;
+--+
| count(*) |
+--+
| 2611 |
+--+
1 row in set (0.01 sec)
mysql select count(*) from Associate;
+--+
| count(*) |
+--+
| 3947 |
+--+
1 row in set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 you have a fundamental misunderstanding about ENUM. You
can set the values, and subsequently the default value of the field as
you wish and then construct your queries based on that.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: enum TRUE/FALSE
I got it made, Active enum('1') works. Thanks everyone!
I had to import old database into MySQL with '1','0' as default. I wanted to
enumerate it so that I can easily use False/true without altering anything
in the current database configurations.
-Original Message-
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 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 you have a fundamental misunderstanding about ENUM. You
can set the values, and subsequently the default value of the field as
you wish and then construct your queries based on that.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 compare against in your field (null, empty string, and 1). If you need three values (like: true, false, don't know) then make 3 enum values. But, regardless of the contents of your ENUM list, I would still make an ENUM field as NOT NULL and would set its default value to one of its enumerated values. That way, the field should only hold what I said it could hold. I agree except that I wouldn't use an ENUM for that. Firstly, it's nonstandard, and secondly, there's a column type which explicitly says what you want, namely BOOL. (Well, it maps to a TINYINT, but that's another story...) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 allow NULL, the NULL value is a legal
value for the column, and the default value is NULL. If an ENUM column
is declared NOT NULL, its default value is the first element of the list
of allowed values.
If you simply want a column to hold boolean data I prefer to use
tiyint(1) unsigned not null
In my application I then treat 0 as False and 1 (or any other number) as
True
This is probably the best way to go.
I do not believe you can make mysql evaluate NULL to be False, mysql
will evaluate 1 = True and 0 = False,
TRUE And FALSE are simply aliases for 1 and 0
Correct. NULL is no value, while FALSE (0) and TRUE (not 0) are values.
Rows with NULL for Active will not match comparisons against TRUE or FALSE
(or any other value).
you have two options
1) You could simply update your table
alter table `Associate` modify `Active` enum('0','1');
(or 1 then 0 if you want true to be the default)
then
update `Associate` set `Active` = 0 where isNull(Active);
2) modify your queries
select count(*) from Associate where Active!=1 OR isNull(Active);
select count(*) from Associate where Active=1;
This makes no sense to me. More to the point, it won't work. ENUMs hold
strings, not numbers. Each string, however, is stored as an integer in your
rows, starting with 1. That's important, because when used in numeric
context you get the position number of the string. In other words, with
Active ENUM('0', '1'), the string '0' is 1 in numeric context and the string
'1' is 2 in numeric context. In other words,
WHERE Active=1
will return the rows with Active = '0', the opposite of what you intended.
The main advantage of using ENUM for boolean values is for human
readability. That is, you define something like
Active ENUM('T', 'F')
or
Active ENUM('yes', 'no')
so that when viewing the data you see the values of Active as text. The
disadvantage of this method is that you must do a string comparison in your
WHERE clause to select rows based on this value (Active = 'T', or Active =
'yes'). If you are going to use '0' and '1' in your ENUM, that advantage
disappears, and you might as well use a TINYINT as you originally suggested,
particularly as it makes your queries simpler (and slightly more efficient).
I recommend you define Active as a TINYINT, then store 1 for true and 0 for
false. Then your queries become
SELECT COUNT(*) FROM Associate WHERE Active;
to find all rows with Active set to true (1), and
SELECT COUNT(*) FROM Associate WHERE NOT Active;
to find all rows with Active set to false (0).
Chris
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 position 0. When you
assign a value to enum_col which isn't in the predefined list, that's the
value you get. Hence,
WHERE enum_col = FALSE
will match rows whose enum_col were assigned values not in the ENUM list.
Note that that means the ENUM column will not only hold what I said it
could hold, as erroneous input is stored as '' (0).
http://dev.mysql.com/doc/mysql/en/ENUM.html
The one way this will work is if you define only the value which should be
true in your ENUM. That is, Active ENUM('1') or Active ENUM('True') or
the like. Then setting Active = TRUE will set Active to the first (only)
string, which is meant to be true, and setting Active = FALSE will set
Active to the empty, error string in position 0. Then
WHERE enum_col = TRUE
and
WHERE enum_col = FALSE
will work as expected.
That said, I still think TINYINT is the way to go.
Michael
[EMAIL PROTECTED] wrote:
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 three values (like: true, false, don't know) then make 3 enum
values. But, regardless of the contents of your ENUM list, I would still
make an ENUM field as NOT NULL and would set its default value to one
of its enumerated values. That way, the field should only hold what I said
it could hold.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Scott Hamm [EMAIL PROTECTED] wrote on 11/10/2004 09:24:33 AM:
I'm trying to figure out how to make Active's null as FALSE and '1' as
TRUE,
in enum point of view
Can anyone help me out here, trying to learn enum's phenomenon? I'm not
sure
I understood document quite clear -- as of yet :(
mysql desc Associate;
+--+--+--+-+-++
| Field| Type | Null | Key | Default | Extra |
+--+--+--+-+-++
| ID | int(11) | | PRI | NULL|
auto_increment |
| LocationID | int(11) | YES | | NULL| |
| NTLogon | varchar(8) | YES | | NULL| |
| DomainID | int(11) | YES | | NULL| |
| LastName | varchar(30) | YES | | NULL| |
| FirstName| varchar(30) | YES | | NULL| |
| Shift| int(11) | YES | | NULL| |
| QADE | enum('','1') | YES | | NULL| |
| DataEntry| enum('','1') | YES | | NULL| |
| QAMR | enum('','1') | YES | | NULL| |
| MailRoom | enum('','1') | YES | | NULL| |
| QAT | enum('','1') | YES | | NULL| |
| Taping | enum('','1') | YES | | NULL| |
| QAF | enum('','1') | YES | | NULL| |
| Filming | enum('','1') | YES | | NULL| |
| CustomerContact | enum('','1') | YES | | NULL| |
| Trainee | enum('','1') | YES | | NULL| |
| Active | enum('','1') | YES | | NULL| |
| Creator | varchar(8) | YES | | NULL| |
| NewAssociateDate | date | YES | | NULL| |
+--+--+--+-+-++
20 rows in set (0.00 sec)
mysql select count(*) from Associate where Active=FALSE;
+--+
| count(*) |
+--+
|0 |
+--+
1 row in set (0.01 sec)
mysql select count(*) from Associate where Active=TRUE;
+--+
| count(*) |
+--+
| 2611 |
+--+
1 row in set (0.01 sec)
mysql select count(*) from Associate;
+--+
| count(*) |
+--+
| 3947 |
+--+
1 row in set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: mysqldump + enum + default + umlaut
Hello miguel,
Tuesday, October 19, 2004, 10:52:03 PM, you wrote:
ms Hi,
ms Thank you for reporting this bug, I already opened the below
ms bug report:
ms http://bugs.mysql.com/bug.php?id=6165
How to repeat:
create table t0 (id int not null auto_increment primary key,
foo enum('a','b','o') default 'o');
http://bugs.mysql.com/bug.php?id=5728
Is this a same bug?
--
Best regards,
Jurimailto:[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump + enum + default + umlaut
Juri Shimon wrote:
Hi,
Hello miguel,
Tuesday, October 19, 2004, 10:52:03 PM, you wrote:
ms Hi,
ms Thank you for reporting this bug, I already opened the below
ms bug report:
ms http://bugs.mysql.com/bug.php?id=6165
How to repeat:
create table t0 (id int not null auto_increment primary key,
foo enum('a','b','o') default 'o');
http://bugs.mysql.com/bug.php?id=5728
Is this a same bug?
According with the user who reported the bug yes. I did the status changes
according his answer.
Regards,
Miguel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select ENUM values
I'm wondering if there is a better way to select the values of a ENUM field. I have a ENUM field called greeting that has 'Mr.','Mrs.','Ms.','Dr.' in it. I need to put these in a HTML select. Right now I'm doing a SHOW COLUMNS FROM global_lead LIKE 'greeting' and then parsing out the response for the enum values (found on mysql site). But I was thinking there might be a more elegant way to do it. Is there? Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select ENUM values
At 11:51 -0700 8/24/04, Michael Pawlowsky wrote: I'm wondering if there is a better way to select the values of a ENUM field. I have a ENUM field called greeting that has 'Mr.','Mrs.','Ms.','Dr.' in it. I need to put these in a HTML select. Right now I'm doing a SHOW COLUMNS FROM global_lead LIKE 'greeting' and then parsing out the response for the enum values (found on mysql site). But I was thinking there might be a more elegant way to do it. Is there? No. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Enum or Int
Hello all I have a table with a field which needs to hold a yes/no or 1/0 value. Which field type is best for this ? Should i create a ENUM field with yes or no values or is it better to create an Int field for this ? What would you recommend ? Best regards Olivier Salzgeber -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 Olivier [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, July 30, 2004 12:48 PM Subject: Enum or Int Hello all I have a table with a field which needs to hold a yes/no or 1/0 value. Which field type is best for this ? Should i create a ENUM field with yes or no values or is it better to create an Int field for this ? What would you recommend ? Best regards Olivier Salzgeber -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 have to explicitly add a contraint to accomplish the same thing . The drawback is that ENUM is not standard SQL making your application non-portable to other RDBMS. On Fri, 2004-07-30 at 08:14, Cemal Dalar wrote: 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 Olivier [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, July 30, 2004 12:48 PM Subject: Enum or Int Hello all I have a table with a field which needs to hold a yes/no or 1/0 value. Which field type is best for this ? Should i create a ENUM field with yes or no values or is it better to create an Int field for this ? What would you recommend ? Best regards Olivier Salzgeber -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 whereas using INT one would have to explicitly add a contraint to accomplish the same thing . You have essentially the same problem with ENUM: | 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 value 0. See http://dev.mysql.com/doc/mysql/en/ENUM.html -- Keith Ivey [EMAIL PROTECTED] Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
BUG in 4.1.3 SHOW CREATE TABLE: enum vs int
Hello mysql,
This bug make impossible to use backup with 'mysqldump','mysql' pair.
How-to-repeat:
---
drop table if exists t1;
drop table if exists t2;
create table t1 (a int, b enum('','')) DEFAULT CHARACTER SET cp1251;
create table t2 (a int not null, b enum('','')) DEFAULT CHARACTER SET cp1251;
show fields from t1 like 'b';
show fields from t2 like 'b';
+---+---+-+--+--+--+
| b | enum('','') | YES | | NULL | |
+---+---+-+--+--+--+
| b | enum('','') | YES | | NULL | | -- Ok
+---+---+-+--+--+--+
show create table t1;
show create table t2;
++
| t1 | CREATE TABLE `t1` (
`a` int(11) default NULL,
`b` enum('','') default NULL
) ENGINE=MyISAM DEFAULT CHARSET=cp1251
++
++
| t2 | CREATE TABLE `t2` (
`a` int(11) NOT NULL default '0',
`b` enum('?','?') default NULL -- Oops!
) ENGINE=MyISAM DEFAULT CHARSET=cp1251
++
--
Best regards,
Juri mailto:[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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=enlr=ie=UTF-8threadm=c6h60e%2419dd%241%40FreeBSD.csie.NCTU.edu.twrnum=1prev=/groups%3Fq%3D%2522enum%2Bor%2Btinyint%2522%26hl%3Den%26lr%3D%26ie%3DUTF-8%26selm%3Dc6h60e%252419dd%25241%2540FreeBSD.csie.NCTU.edu.tw%26rnum%3D1
(thread is titled enum or tinyint)
I hope that helps!
On Thu, 24 Jun 2004 21:17:09 +0300, Cemal Dalar [EMAIL PROTECTED] wrote:
Hi all,
I need a boolean column and at to this time I always used ENUM('Y','N')
for this. I'am wondering that will there be a performance difference between
using ENUM('Y','N') and TINYINT(1) or BOOLEAN?. And put 0 or 1 to TINYINT
column.
Best Regards,
Cemal Dalar a.k.a Jimmy
System Administrator Web Developer
http://www.dalar.net
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ENUM vs TINYINT
If you want to use as little space as possible use char(0) and null/not-null
for your boolean values:
CREATE TEMPORARY TABLE test_bool (i char(5), bool char(0));
INSERT INTO test_bool VALUES
('true',''),('false',null),('true',''),('true',''),('false',null);
SELECT * FROM test_bool WHERE bool is not Null;
SELECT * FROM test_bool WHERE bool is Null;
In a multi-column table that saves you 1 byte per row.
Regards,
Tim.
- Original Message -
From: Brian Mansell [EMAIL PROTECTED]
To: 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:
http://groups.google.com/groups?hl=enlr=ie=UTF-8threadm=c6h60e%2419dd%241%40FreeBSD.csie.NCTU.edu.twrnum=1prev=/groups%3Fq%3D%2522enum%2Bor%2Btinyint%2522%26hl%3Den%26lr%3D%26ie%3DUTF-8%26selm%3Dc6h60e%252419dd%25241%2540FreeBSD.csie.NCTU.edu.tw%26rnum%3D1
(thread is titled enum or tinyint)
I hope that helps!
On Thu, 24 Jun 2004 21:17:09 +0300, Cemal Dalar [EMAIL PROTECTED] wrote:
Hi all,
I need a boolean column and at to this time I always used
ENUM('Y','N')
for this. I'am wondering that will there be a performance difference
between
using ENUM('Y','N') and TINYINT(1) or BOOLEAN?. And put 0 or 1 to
TINYINT
column.
Best Regards,
Cemal Dalar a.k.a Jimmy
System Administrator Web Developer
http://www.dalar.net
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ENUM vs TINYINT
Hi all,
I need a boolean column and at to this time I always used ENUM('Y','N')
for this. I'am wondering that will there be a performance difference between
using ENUM('Y','N') and TINYINT(1) or BOOLEAN?. And put 0 or 1 to TINYINT
column.
Best Regards,
Cemal Dalar a.k.a Jimmy
System Administrator Web Developer
http://www.dalar.net
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error 1064 (Syntax error) after adding ENUM or Set types to table definition
I created the dump file from my current DB to make adjustments and also migrate to
Innodb tables. In the process I have tried to add some ENUM types in select table.
Obviously, I am doing something wrong as each one give me a syntax error 1064 when I
try to source in the text file. I've read the manual and do not see where my error is.
Can someone point out what is wrong on the below table?
Thanks in advance
Bob
-- MySQL dump 9.10
--
-- Host: bartis-1Database: stingertrdb4
-- --
-- Server version 4.0.18-max-debug
--
-- Table structure for table `feature_list`
--
DROP TABLE IF EXISTS feature_list;
CREATE TABLE feature_list (
Feature varchar(50) default NOT NULL,
New ENUM('Y','N') NOT NULL,
Owner varchar(50) default NOT NULL,
NewFeatureDescription varchar(255) default NOT NULL,
DateMod datetime NOT NULL default '-MM-DD HH:MM:SS',
PRIMARY KEY Feature (Feature),
INDEX Owner (Owner),
FOREIGN KEY (Owner) REFERENCES tester_list(Tester) ON UPDATE CASCADE ON DELETE
RESTRICT
) TYPE=InnoDB;
Robert M. Bartis
Lucent Technologies ¢
Room HO 1C-413A (HO) / 1B-304 (WH)
( 732.949.4565 (HO) / 973.386.7426 (WH)
* [EMAIL PROTECTED]
Re: Error 1064 (Syntax error) after adding ENUM or Set types to table definition
One of the tricky things about finding errors is that the error is often
earlier than you expect. Mysql points out where it stopped understanding
you, but often it's due to something on the line before. That's the case
here. There's nothing wrong with your ENUM, but the previous line says you
want to set a DEFAULT for column Feature, but you don't provide a value.
You need to either provide the default value or drop the word DEFAULT. The
same thing applies to columns Owner and NewFeatureDescription.
Michael
Bartis, Robert M (Bob) wrote:
I created the dump file from my current DB to make adjustments and also migrate to Innodb tables. In the process I have tried to add some ENUM types in select table. Obviously, I am doing something wrong as each one give me a syntax error 1064 when I try to source in the text file. I've read the manual and do not see where my error is. Can someone point out what is wrong on the below table?
Thanks in advance
Bob
-- MySQL dump 9.10
--
-- Host: bartis-1Database: stingertrdb4
-- --
-- Server version 4.0.18-max-debug
--
-- Table structure for table `feature_list`
--
DROP TABLE IF EXISTS feature_list;
CREATE TABLE feature_list (
Feature varchar(50) default NOT NULL,
New ENUM('Y','N') NOT NULL,
Owner varchar(50) default NOT NULL,
NewFeatureDescription varchar(255) default NOT NULL,
DateMod datetime NOT NULL default '-MM-DD HH:MM:SS',
PRIMARY KEY Feature (Feature),
INDEX Owner (Owner),
FOREIGN KEY (Owner) REFERENCES tester_list(Tester) ON UPDATE CASCADE ON DELETE RESTRICT
) TYPE=InnoDB;
Robert M. Bartis
Lucent Technologies ¢
Room HO 1C-413A (HO) / 1B-304 (WH)
( 732.949.4565 (HO) / 973.386.7426 (WH)
* [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: enum or tinyint?
Harald Fuchs wrote: snip 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 rows affected (0.00 sec) mysql DESCRIBE bt; +---++--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---++--+-+-+---+ | flag | tinyint(1) | YES | | NULL| | +---++--+-+-+---+ 1 row in set (0.00 sec) mysql SHOW CREATE TABLE bt; +---++ | Table | Create Table | +---++ | bt| CREATE TABLE `bt` ( `flag` tinyint(1) default NULL ) TYPE=MyISAM | +---++ 1 row in set (0.00 sec) Which is probably just as well, I think. Otherwise, you might be surprised to find that 13, for example, is a legal BOOL value. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
enum or tinyint?
What is recommanded for a large database to use enum or tinyint? Should I store active inactive or tinyint (1) with 0 and 1? Thanks, Arthur -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 question is moot.
Internally, enum values are stored as ints (tinyints, in this case), so the
speed question is nearly moot. That is, while WHERE status='active' may
look like a string comparison, 'active' is actually converted to an int
(position in the enum list) which is used for the comparison. So, the enum
has a miniscule amount of overhead (look up the position) then integer
comparisons just like the tinyint. I'd be surprised if you noticed a
difference, regardless of table size.
That leaves ease of use, which is a matter of preference and not, I think, a
table size issue. Personally, I like to use tinyints for boolean flags like
this. To me, they are simpler and self-documenting. With values 'active'
and 'inactive', I would probably define the column as active TINYINT, then
use 1 for active and 0 for inactive. That way, I can simply write queries like
SELECT * FROM mytable WHERE active;
or
SELECT * FROM mytable WHERE NOT active;
On the other hand, there is nothing in the column definition to stop someone
from setting active to 3 (or 5, or 18...) in an insert or update, and those
wrong values are indistinguishable from 1 in my WHERE clauses above, so I
have to properly handle this possibility in my code.
The alternative would be to define the column as something like:
status ENUM('active', 'inactive')
In this case, active is 1 and inactive is 2, both of which are true, so you
have to explicitly compare them to values:
SELECT * FROM mytable WHERE status='active';
or
SELECT * FROM mytable WHERE status='inactive';
You are somewhat protected from bad values, however. Attempts to insert or
update status to values other than those defined won't raise an error, but
they will result in an empty string (position 0) being stored, which won't
match either of the above WHERE clauses. Another advantage is flexibility.
Should you ever decide you need a 3rd status, it would be easy to add it
to your ENUM.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to select and set enum by string value
I've been unable to select or set an enum field by using the string values.
Numeric values work correctly.
SHOW CREATE TABLE gives the following:
CREATE TABLE `Organizations` (
`OrgID` int(11) NOT NULL auto_increment,
`Organization` varchar(50) NOT NULL default '',
`TypeOrg` enum('Customer',' Vendor',' ThisOrg',' Other') NOT
NULL default 'Customer',
PRIMARY KEY (`OrgID`),
UNIQUE KEY `Name` (`Organization`)
) TYPE=MyISAM COMMENT='Organization Information'
But SELECT * FROM Organizations WHERE TypeOrg = 'ThisOrg' gives zero
results even though using TypeOrg = 3 works. Similarly, UPDATE
Organizations SET TypeOrg = 'ThisOrg' WHERE OrgID = 1 sets the value of
TypeOrg to 0.
How do I get these to work with string values?
Thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to select and set enum by string value
* Eldon Ziegler
I've been unable to select or set an enum field by using the
string values.
Numeric values work correctly.
SHOW CREATE TABLE gives the following:
CREATE TABLE `Organizations` (
`OrgID` int(11) NOT NULL auto_increment,
`Organization` varchar(50) NOT NULL default '',
`TypeOrg` enum('Customer',' Vendor',' ThisOrg',' Other') NOT
'ThisOrg' is not the same as ' ThisOrg', note the leading space. You must
use ' ThisOrg' in your query, or change your enum to contain values without
a leading space.
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Too many columns error when altering column type to ENUM?
I've seen a number of references to the Too many columns error, but I'm encountering something that appears to be different. Basically, I'm trying to change a single column in a table (that has 20 columns) from VARCHAR(255) to ENUM(...) where the enum list contains about 900 entries, each a string of about 50 characters or so. From what I understand the enum limit is about 65000, and the number of enum columns in a single table is about 255, but since I'm looking at values of 900 and ~5, respectively, I don't think I'm hitting these limits. Any ideas why I'm getting this error? I'm using version 4.0.13. Thanks.
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 https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
enum version info
Hi, Quick question since I can't see to find version information online. What version was enum first supported under? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Enum default values
Hi,
I'm having problems with setting default values for an enum. It's for
a booking system, and I'm keeping track of which day a property starts
a booking period. It's useful to have the enum in day order therefore.
Omitting the prompts, here's what's happening;
*
drop table property;
create table property (
property_id int(10) unsigned NOT NULL auto_increment,
name char(30) default NULL,
changeday enum('Sun','Mon','Tue','Wed','Thu','Fri','Sat') not null
default 'Sat',
PRIMARY KEY (property_id)
);
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 into property values ('','Heathside','');
select * from property;
+-+---+---+
| property_id | name | changeday |
+-+---+---+
| 1 | Book-keeper's Cottage | Thu |
| 2 | Inglenook Barn| Fri |
| 3 | Maggie's House| Fri |
| 4 | Riverside View| |
| 5 | The Manse | |
| 6 | Heathside | |
+-+---+---+
6 rows in set (0.00 sec)
and...
show create table property\G
*** 1. row ***
Table: property
Create Table: CREATE TABLE `property` (
`property_id` int(10) unsigned NOT NULL auto_increment,
`name` char(30) default NULL,
`changeday` enum('Sun','Mon','Tue','Wed','Thu','Fri','Sat') NOT NULL
default 'Sat',
PRIMARY KEY (`property_id`)
) TYPE=MyISAM
1 row in set (0.01 sec)
shows that what I think is a legal definition for the table is what's
being recorded.
So no luck with default value being specified, i.e. Sat is not showing
up in the changeday column for the last three properties. I've tried
changing the enum defn statement to
changeday enum('Sun','Mon','Tue','Wed','Thu','Fri','Sat') not null,
and
changeday enum('Sun','Mon','Tue','Wed','Thu','Fri','Sat') default
'Sat' not null, // which I'm sure is syntactically wrong anyway
just to see if I could get Sunday to show up, and no luck either. What
am I missing?
I'm running 4.0.16-standard server, under OS X 10.3.1, via the terminal.
Any help gratefully received!
rgrds
R
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 into property values ('','Heathside','');
select * from property;
+-+---+---+
| property_id | name | changeday |
+-+---+---+
| 1 | Book-keeper's Cottage | Thu |
| 2 | Inglenook Barn| Fri |
| 3 | Maggie's House| Fri |
| 4 | Riverside View| |
| 5 | The Manse | |
| 6 | Heathside | |
+-+---+---+
6 rows in set (0.00 sec)
[/snip]
All expected and correct behaviour. Why? Because you're specifying a
blank for the third value in the insert statement.
insert into property values ('','Heathside','');
insert into property values BLANK, Heathside, BLANK;
A proper insert looks like this (without spec'ing a changeday)
INSERT INTO property (property_id, name)
VALUES ('', 'Heathside')
Try that and let us know.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 possible. I will now have to supply a
field list to the function in addition. Ah well ;-)
Apologies for wasting your time with such a newbie-esque question.
rgrds
dd
On 5 Jan 2004, at 12:51, Jay Blanchard wrote:
[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 into property values ('','Heathside','');
select * from property;
+-+---+---+
| property_id | name | changeday |
+-+---+---+
| 1 | Book-keeper's Cottage | Thu |
| 2 | Inglenook Barn| Fri |
| 3 | Maggie's House| Fri |
| 4 | Riverside View| |
| 5 | The Manse | |
| 6 | Heathside | |
+-+---+---+
6 rows in set (0.00 sec)
[/snip]
All expected and correct behaviour. Why? Because you're specifying a
blank for the third value in the insert statement.
insert into property values ('','Heathside','');
insert into property values BLANK, Heathside, BLANK;
A proper insert looks like this (without spec'ing a changeday)
INSERT INTO property (property_id, name)
VALUES ('', 'Heathside')
Try that and let us know.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 Manse','');
insert into property values ('','Heathside','');
select * from property;
+-+---+---+
| property_id | name | changeday |
+-+---+---+
| 1 | Book-keeper's Cottage | Thu |
| 2 | Inglenook Barn| Fri |
| 3 | Maggie's House| Fri |
| 4 | Riverside View| |
| 5 | The Manse | |
| 6 | Heathside | |
+-+---+---+
6 rows in set (0.00 sec)
[/snip]
All expected and correct behaviour. Why? Because you're specifying a
blank for the third value in the insert statement.
insert into property values ('','Heathside','');
insert into property values BLANK, Heathside, BLANK;
Yes, the DEFAULT doesn't apply. However, shouldn't MySQL
raise an exception because '' isn't a valid value for this ENUM
specification?
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL
Server.
Upscene Productions
http://www.upscene.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 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 value 0. More about this later. If an ENUM is declared NULL, NULL is also a legal value for the column, and the default value is NULL. If an ENUM is declared NOT NULL, the default value is the first element of the list of allowed values. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 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
value 0. More about this later.
If an ENUM is declared NULL, NULL is also a legal value for the column,
and the default value is NULL. If an ENUM is declared NOT NULL, the
default value is the first element of the list of allowed values.
ok, but the table was defined as:
create table property (
property_id int(10) unsigned NOT NULL auto_increment,
name char(30) default NULL,
changeday enum('Sun','Mon','Tue','Wed','Thu','Fri','Sat') not null
default 'Sat',
PRIMARY KEY (property_id)
);
changeday is NOT NULL - but you still can enter
'' ... Strange and, IMO, a bug. What's the use of
restricting to a set of possible values without enforcing it?
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL
Server.
Upscene Productions
http://www.upscene.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 value 0. More about this later. changeday is NOT NULL - but you still can enter '' ... Strange and, IMO, a bug. What's the use of restricting to a set of possible values without enforcing it? [/snip] On an INSERT it allows you to get or track entry errors. You have to apply error checking to your application to enforce the integrity of the enum field. This has been one of those excessively debated issues over the years. I have used this 'feature' for error checking for a long time now. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 the numerical value 0. More about this later. changeday is NOT NULL - but you still can enter '' ... Strange and, IMO, a bug. What's the use of restricting to a set of possible values without enforcing it? [/snip] On an INSERT it allows you to get or track entry errors. You have to apply error checking to your application to enforce the integrity of the enum field. So, basically, an ENUM has no real use when it comes to checking its values? This has been one of those excessively debated issues over the years. I sure hope so :-) ... I'm still new to MySQL though :-) I have used this 'feature' for error checking for a long time With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com now. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 has the numerical value 0. More about this later. changeday is NOT NULL - but you still can enter '' ... Strange and, IMO, a bug. What's the use of restricting to a set of possible values without enforcing it? [/snip] On an INSERT it allows you to get or track entry errors. You have to apply error checking to your application to enforce the integrity of the enum field. So, basically, an ENUM has no real use when it comes to checking its values? This has been one of those excessively debated issues over the years. I sure hope so :-) ... I'm still new to MySQL though :-) I have used this 'feature' for error checking for a long time 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 http://www.mysql.com/doc/en/constraint_NOT_NULL.html: To be able to support easy handling of non-transactional tables all fields in MySQL have default values. If you insert a 'wrong' value in a column like a NULL in a NOT NULL column or a too big numerical value in a numerical column, MySQL will instead of giving an error instead set the column to the 'best possible value'. In the case of ENUMs, best possible means the special error value which is 0 in numeric context and '' in string context. Supporting non-transactional tables is the key here. Throwing an error in the middle of a multi row insert is a problem if you cannot roll back. Hence, data integrity checking is the responsibility of the client/programmer. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 http://www.mysql.com/doc/en/constraint_NOT_NULL.html: Indeed. One of those weird MySQL things. Is this different on InnoDB? To be able to support easy handling of non-transactional tables all fields in MySQL have default values. If you insert a 'wrong' value in a column like a NULL in a NOT NULL column or a too big numerical value in a numerical column, MySQL will instead of giving an error instead set the column to the 'best possible value'. In the case of ENUMs, best possible means the special error value which is 0 in numeric context and '' in string context. Well, so it seems. We learn new things every day. Supporting non-transactional tables is the key here. Throwing an error in the middle of a multi row insert is a problem if you cannot roll back. Time to phase out those tables :-) Hence, data integrity checking is the responsibility of the client/programmer. Ouch. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: Enum default values
Hi,
I will now have to supply a field list to the function in
addition. Ah well ;-)
Maybe this help:
insert into `property` values ('', 'Riverside View', default);
Take care,
Aleksandar
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re[2]: Enum default values
Ah ha!
Thank you Aleksandar. That's a much better solution. In fact, now
youv'e told me this, I looked in the Manual, and the scales where
lifted from my eyes. You've saved me re-writing (and re-thinking) a
large chunk of PHP.
For anyone else's future reference it's all there in plain english -
which confirms me as an idiot.
6.4.3 INSERT Syntax
INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name [(col_name,...)]
VALUES ((expression | DEFAULT),...),(...),...
[ ON DUPLICATE KEY UPDATE col_name=expression, ... ]
or INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name [(col_name,...)]
SELECT ...
or INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name
SET col_name=(expression | DEFAULT), ...
[ ON DUPLICATE KEY UPDATE col_name=expression, ... ]
and in the not so small print ;-)
Any column not explicitly given a value is set to its default value.
For example, if you specify a column list that doesn't name all the
columns in the table, unnamed columns are set to their default values.
Default value assignment is described in section 6.5.3 CREATE TABLE
Syntax. You can also use the keywordDEFAULT to set a column to its
default value. (New in MySQL 4.0.3.) This makes it easier to write
INSERTstatements that assign values to all but a few columns, because
it allows you to avoid writing an incompleteVALUES() list (a list that
does not include a value for each column in the table). Otherwise, you
would have to write out the list of column names corresponding to each
value in the VALUES() list
Which is of course what I was trying to get at! ;-)
Thanks again.
R
On 5 Jan 2004, at 17:37, Aleksandar Bradaric wrote:
insert into `property` values ('', 'Riverside View', default);
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Invalid ENUM values after upgrading from 4.0 to 4.1
I have the same problem with cp1251 and, seems, it's a bug. I have posted correspoding message two days ago. Without any reply 8((( I migrate 3.23.55 - 4.1.1 and I don't know about existing such problem in 4.0. Look in your table.frm - your field is in correct charset. More of, inserts and updates of your tables are working correct (check it ;). The problem, IMHO, is in 'show columns ...' implementation. For me, it's critically (in my apps, I grab allowed values from table structure). Hi, I'm facing a problem with the new 4.1 branch. I've built MySQL from sources with default-language=hebrew and extra-languages=utf. I've also used default-collation=hebrew_general_ci. After installing, this is what I get for few of the variables: mysql show variables like '%char%'; +--++ | Variable_name| Value | +--++ | character_set_server | hebrew | | character_set_system | utf8 | | character_set_database | hebrew | | character_set_client | hebrew | | character_set_connection | hebrew | | character-sets-dir | /usr/local/mysql/share/mysql/charsets/ | | character_set_results| hebrew | +--++ 7 rows in set (0.00 sec) mysql show variables like '%coll%'; +--+---+ | Variable_name| Value | +--+---+ | collation_connection | hebrew_general_ci | | collation_database | hebrew_general_ci | | collation_server | hebrew_general_ci | +--+---+ 3 rows in set (0.00 sec) Sounds great, right? But when I try to work with databases, and their tables, that were upgraded from 4.0 with the new 4.1 server, I see question marks (???) for values in ENUM() field type. By the way, I noticed that default values for such fields (when having ``NOT NULL``) display perfectly in Hebrew. Explanation? Is there any specific ``upgrade procedure`` I should do to overcome this problem? Thanks in advance. Noor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Invalid ENUM values after upgrading from 4.0 to 4.1
It's known bug, will be fixed in 4.1.2 - Original Message - From: Juri Shimon [EMAIL PROTECTED] To: [EMAIL PROTECTED]; Noor Dawod [EMAIL PROTECTED] Sent: Wednesday, December 17, 2003 12:37 PM Subject: Re: Invalid ENUM values after upgrading from 4.0 to 4.1 I have the same problem with cp1251 and, seems, it's a bug. I have posted correspoding message two days ago. Without any reply 8((( I migrate 3.23.55 - 4.1.1 and I don't know about existing such problem in 4.0. Look in your table.frm - your field is in correct charset. More of, inserts and updates of your tables are working correct (check it ;). The problem, IMHO, is in 'show columns ...' implementation. For me, it's critically (in my apps, I grab allowed values from table structure). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Invalid ENUM values after upgrading from 4.0 to 4.1
Hi, I'm facing a problem with the new 4.1 branch. I've built MySQL from sources with default-language=hebrew and extra-languages=utf. I've also used default-collation=hebrew_general_ci. After installing, this is what I get for few of the variables: mysql show variables like '%char%'; +--++ | Variable_name| Value | +--++ | character_set_server | hebrew | | character_set_system | utf8 | | character_set_database | hebrew | | character_set_client | hebrew | | character_set_connection | hebrew | | character-sets-dir | /usr/local/mysql/share/mysql/charsets/ | | character_set_results| hebrew | +--++ 7 rows in set (0.00 sec) mysql show variables like '%coll%'; +--+---+ | Variable_name| Value | +--+---+ | collation_connection | hebrew_general_ci | | collation_database | hebrew_general_ci | | collation_server | hebrew_general_ci | +--+---+ 3 rows in set (0.00 sec) Sounds great, right? But when I try to work with databases, and their tables, that were upgraded from 4.0 with the new 4.1 server, I see question marks (???) for values in ENUM() field type. By the way, I noticed that default values for such fields (when having ``NOT NULL``) display perfectly in Hebrew. Explanation? Is there any specific ``upgrade procedure`` I should do to overcome this problem? Thanks in advance. Noor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
BUGS: MySQL 4.1.1 Internationalization (ENUM)
Hi!
create table t(a enum ('','',''));
show create table t;
CREATE TABLE `t` (
`a` enum('','???','???') default NULL
) TYPE=MyISAM DEFAULT CHARSET=cp1251
and this is a bug!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
