quoting strings/ints/vars in mysql
hi... i've been lloking over various open source apps to get a feel for how the apps store and manipulate information as it's stored in the mysql db... i see some apps that put 's around strings that get stored in the db, as well as some apps that appear to put 's around numeric data... i'm trying to determine what's the best/correct approach. if i have a char data, should i place data in the colume that's been enclosed by 's? similarly, if i have numeric/date information, should it be enclosed in 's?? i'm currently looking through the mysql docs... thoughts/comments/etc... thanks -bruce [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: quoting strings/ints/vars in mysql
bruce wrote: hi... i've been lloking over various open source apps to get a feel for how the apps store and manipulate information as it's stored in the mysql db... i see some apps that put 's around strings that get stored in the db, as well as some apps that appear to put 's around numeric data... i'm trying to determine what's the best/correct approach. if i have a char data, should i place data in the colume that's been enclosed by 's? similarly, if i have numeric/date information, should it be enclosed in 's?? i'm currently looking through the mysql docs... thoughts/comments/etc... thanks -bruce [EMAIL PROTECTED] 'This is a string' This is not. Number - 12345 String- '12345' String columns should get strings. If you forget the ' marks, mysql will think the string is a field name. Numeric columns should get numbers. If you put ' marks around numbers, MySql will convert it. String conversions in select statements will slow things down. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: quoting strings/ints/vars in mysql
bruce [EMAIL PROTECTED] wrote on 09/23/2005 12:24:25 PM:
hi...
i've been lloking over various open source apps to get a feel for how
the
apps store and manipulate information as it's stored in the mysql db...
i see some apps that put 's around strings that get stored in the db,
as
well as some apps that appear to put 's around numeric data...
i'm trying to determine what's the best/correct approach.
if i have a char data, should i place data in the colume that's been
enclosed by 's?
similarly, if i have numeric/date information, should it be enclosed in
's??
i'm currently looking through the mysql docs...
thoughts/comments/etc...
thanks
-bruce
[EMAIL PROTECTED]
Here is the official reference for how to represent all of the MySQL data
types as literals:
http://dev.mysql.com/doc/mysql/en/literals.html
Short and simple:
STRINGS and DATES get quoted ('). If a string literal needs to contain any
of a list of special characters (TAB, CR, LF, NULL, \, ', EOF, , etc.)
you must escape that character with an escape sequence. For instance if
you wanted to insert the phrase Isn't it lovely? into a text field you
would use a SQL statement like this:
INSERT tablename (textfieldname, ... other columns...)
VALUES ('Isn\'t it lovely?', ... other values ...);
NUMBERS do not get quoted. Anyone quoting numbers is killing their
application performance by causing at least one extraneous type conversion
per assignment/comparison. Numbers include: integers, decimal values,
scientific values.
Full details are in the reading.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
