RE: Varchar limit warning - similar question
The "length" of the numeric type defines the way it will be displayed. The storage requirement is defined by the numeric data type (tinyint, etc.). I don't think there's any way to have MySQL limit input data to the "length" you use. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 > -Original Message- > From: Olaf Stein [mailto:[EMAIL PROTECTED] > Sent: Friday, January 05, 2007 2:26 PM > To: Jerry Schwartz > Subject: Re: Varchar limit warning - similar question > > So what effect does e.g. int(3) have compared with just int > > > Thanks > Olaf > > > On 1/5/07 2:24 PM, "Jerry Schwartz" > <[EMAIL PROTECTED]> wrote: > > > For limiting possible values to 0, 1, 2, 3, 4, 5 you could > use an ENUM, > > although that is not advised because you're using numeric > indices to refer > > to numeric values and that can be confusing. > > > > Regards, > > > > Jerry Schwartz > > Global Information Incorporated > > 195 Farmington Ave. > > Farmington, CT 06032 > > > > 860.674.8796 / FAX: 860.674.8341 > > > > > >> -----Original Message- > >> From: Olaf Stein [mailto:[EMAIL PROTECTED] > >> Sent: Friday, January 05, 2007 1:52 PM > >> To: [EMAIL PROTECTED] > >> Cc: MySql > >> Subject: Re: Varchar limit warning - similar question > >> > >> Thanks > >> That works nicely. > >> > >> Why does that not have any effect with numeric values. > >> E.g. I can insert the same max numbers in a tinyint column > then in a > >> tinyint(1) column, > >> > >> For example, how do I create a column that only allows 1 > >> numeric value from > >> 0 to 5? > >> > >> Thanks > >> Olaf > >> > >> > >> On 1/5/07 12:52 PM, "[EMAIL PROTECTED]" > >> <[EMAIL PROTECTED]> wrote: > >> > >>> > >>> You need to set the sql_mode to STRICT_TRANS_TABLES or > >> STRICT_ALL_TABLES. We > >>> set this for the server in the my.cnf file. Be careful, > >> though, because there > >>> are some third-party GUI clients that don't read the > >> my.cnf file and thus > >>> don't set the sql_mode to what you expect. In that case, > >> set it yourself in > >>> the client: > >>> > >>> set SESSION sql_mode='STRICT_TRANS_TABLES'; > >>> select @@session.sql_mode; > >>> > >>> You can read about sql_mode here. > >>> > >>> http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html > >>> > >>> Donna > >>> > >>> > >>> Olaf Stein <[EMAIL PROTECTED]> 01/05/2007 12:37 PM > >>> To > >>> MySql > >>> cc > >>> Subject > >>> Varchar limit warning > >>> > >>> > >>> > >>> > >>> Hi all > >>> > >>> If I insert a value to great for a field (e.g. '123456' > >> into a varchar(5) > >>> field), mysql runs the insert without warning or error and > >> cuts of what > >>> doesn't fit. > >>> > >>> How can I tell it to launch an error and abort the insert? > >>> > >>> Thanks > >>> Olaf > >>> > >> > >> > >> - > >> Olaf Stein > >> DBA > >> Center for Quantitative and Computational Biology > >> Columbus Children's Research Institute > >> 700 Children's Drive > >> phone: 1-614-355-5685 > >> cell: 1-614-843-0432 > >> email: [EMAIL PROTECTED] > >> > >> > > > > > > > > - > Olaf Stein > DBA > Center for Quantitative and Computational Biology > Columbus Children's Research Institute > 700 Children's Drive > phone: 1-614-355-5685 > cell: 1-614-843-0432 > email: [EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Varchar limit warning - similar question
For limiting possible values to 0, 1, 2, 3, 4, 5 you could use an ENUM, although that is not advised because you're using numeric indices to refer to numeric values and that can be confusing. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 > -Original Message- > From: Olaf Stein [mailto:[EMAIL PROTECTED] > Sent: Friday, January 05, 2007 1:52 PM > To: [EMAIL PROTECTED] > Cc: MySql > Subject: Re: Varchar limit warning - similar question > > Thanks > That works nicely. > > Why does that not have any effect with numeric values. > E.g. I can insert the same max numbers in a tinyint column then in a > tinyint(1) column, > > For example, how do I create a column that only allows 1 > numeric value from > 0 to 5? > > Thanks > Olaf > > > On 1/5/07 12:52 PM, "[EMAIL PROTECTED]" > <[EMAIL PROTECTED]> wrote: > > > > > You need to set the sql_mode to STRICT_TRANS_TABLES or > STRICT_ALL_TABLES. We > > set this for the server in the my.cnf file. Be careful, > though, because there > > are some third-party GUI clients that don't read the > my.cnf file and thus > > don't set the sql_mode to what you expect. In that case, > set it yourself in > > the client: > > > > set SESSION sql_mode='STRICT_TRANS_TABLES'; > > select @@session.sql_mode; > > > > You can read about sql_mode here. > > > > http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html > > > > Donna > > > > > > Olaf Stein <[EMAIL PROTECTED]> 01/05/2007 12:37 PM > > To > > MySql > > cc > > Subject > > Varchar limit warning > > > > > > > > > > Hi all > > > > If I insert a value to great for a field (e.g. '123456' > into a varchar(5) > > field), mysql runs the insert without warning or error and > cuts of what > > doesn't fit. > > > > How can I tell it to launch an error and abort the insert? > > > > Thanks > > Olaf > > > > > - > Olaf Stein > DBA > Center for Quantitative and Computational Biology > Columbus Children's Research Institute > 700 Children's Drive > phone: 1-614-355-5685 > cell: 1-614-843-0432 > email: [EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Varchar limit warning - similar question
Sorry, wrong example For instance, it makes no difference regarding the values I can enter if I declare a field as int or int(3) On 1/5/07 1:52 PM, "Olaf Stein" <[EMAIL PROTECTED]> wrote: > Thanks > That works nicely. > > Why does that not have any effect with numeric values. > E.g. I can insert the same max numbers in a tinyint column then in a > tinyint(1) column, > > For example, how do I create a column that only allows 1 numeric value from > 0 to 5? > > Thanks > Olaf > > > On 1/5/07 12:52 PM, "[EMAIL PROTECTED]" > <[EMAIL PROTECTED]> wrote: > >> >> You need to set the sql_mode to STRICT_TRANS_TABLES or STRICT_ALL_TABLES. We >> set this for the server in the my.cnf file. Be careful, though, because >> there >> are some third-party GUI clients that don't read the my.cnf file and thus >> don't set the sql_mode to what you expect. In that case, set it yourself in >> the client: >> >> set SESSION sql_mode='STRICT_TRANS_TABLES'; >> select @@session.sql_mode; >> >> You can read about sql_mode here. >> >> http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html >> >> Donna >> >> >> Olaf Stein <[EMAIL PROTECTED]> 01/05/2007 12:37 PM >> To >> MySql >> cc >> Subject >> Varchar limit warning >> >> >> >> >> Hi all >> >> If I insert a value to great for a field (e.g. '123456' into a varchar(5) >> field), mysql runs the insert without warning or error and cuts of what >> doesn't fit. >> >> How can I tell it to launch an error and abort the insert? >> >> Thanks >> Olaf >> > > > - > Olaf Stein > DBA > Center for Quantitative and Computational Biology > Columbus Children's Research Institute > 700 Children's Drive > phone: 1-614-355-5685 > cell: 1-614-843-0432 > email: [EMAIL PROTECTED] > - Olaf Stein DBA Center for Quantitative and Computational Biology Columbus Children's Research Institute 700 Children's Drive phone: 1-614-355-5685 cell: 1-614-843-0432 email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Varchar limit warning - similar question
Thanks That works nicely. Why does that not have any effect with numeric values. E.g. I can insert the same max numbers in a tinyint column then in a tinyint(1) column, For example, how do I create a column that only allows 1 numeric value from 0 to 5? Thanks Olaf On 1/5/07 12:52 PM, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > > You need to set the sql_mode to STRICT_TRANS_TABLES or STRICT_ALL_TABLES. We > set this for the server in the my.cnf file. Be careful, though, because there > are some third-party GUI clients that don't read the my.cnf file and thus > don't set the sql_mode to what you expect. In that case, set it yourself in > the client: > > set SESSION sql_mode='STRICT_TRANS_TABLES'; > select @@session.sql_mode; > > You can read about sql_mode here. > > http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html > > Donna > > > Olaf Stein <[EMAIL PROTECTED]> 01/05/2007 12:37 PM > To > MySql > cc > Subject > Varchar limit warning > > > > > Hi all > > If I insert a value to great for a field (e.g. '123456' into a varchar(5) > field), mysql runs the insert without warning or error and cuts of what > doesn't fit. > > How can I tell it to launch an error and abort the insert? > > Thanks > Olaf > - Olaf Stein DBA Center for Quantitative and Computational Biology Columbus Children's Research Institute 700 Children's Drive phone: 1-614-355-5685 cell: 1-614-843-0432 email: [EMAIL PROTECTED]
