Re: Choosing between VARCHAR and TEXT
At 9:34 -0600 11/21/03, Paul Fine wrote:
Can anyone tell me what is better to use for items such as names and
addresses? I suspect VARCHAR(max anticipated length) but why? Is it because
Text will pad?
What do you mean by Text will pad? I can think of two distinct
possibilities,
only one of which is true.
One difference between VARCHAR and TEXT is that trailing whitespace truncation
occurs for VARCHAR, but not for TEXT. This may be significant for your
applications.
These queries issue the distinction:
DROP TABLE IF EXISTS t;
CREATE TABLE t (ch VARCHAR(255), tx TINYTEXT);
INSERT INTO t (ch, tx) VALUES('x ','x ');
SELECT LENGTH(ch), LENGTH(tx) FROM t;
The ch and tx columns both have a maximum length of 255 bytes, so they
are equivalent in terms of what kinds of values you can store into them.
But some values will be treated differently. The result of the SELECT
is:
+++
| LENGTH(ch) | LENGTH(tx) |
+++
| 1 | 3 |
+++
--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Choosing between VARCHAR and TEXT
Can anyone tell me what is better to use for items such as names and addresses? I suspect VARCHAR(max anticipated length) but why? Is it because Text will pad? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Choosing between VARCHAR and TEXT
Will any names or addresses exceed 255 characters? The documentation recommends to use the smallest column size possible. -will Real-time Chat: irc.freenode.net - #mysql ( http://www.mysql.com/doc/en/IRC.html ) - Original Message - From: Paul Fine [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, November 21, 2003 7:34 AM Subject: Choosing between VARCHAR and TEXT Can anyone tell me what is better to use for items such as names and addresses? I suspect VARCHAR(max anticipated length) but why? Is it because Text will pad? Thanks! -- 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: Choosing between VARCHAR and TEXT
It is always best to set fields sizes as small as possible, to both conserve disk space, as well as improve performance. The smaller the record the faster the queries. While you do not want to make any field too small, you also don't want to make it too big. With current databases, you can easily change field size after designing without causing major problems. Old database languages did not allow you to change field sizes on the fly. You had to redesign the database, then write a program to upgrade the database, then modify your programs to tell it the changes in your database. Consider your output, typically when printing an address onto a label, you can only write approx 35 characters, unless you are using very large shipping labels. I usually define for addresses the following: add1 varchar(35) add2 varchar(35) city varchar(25) state char(2) zip varchar(10) Total space 107 characters max At 11:05 AM 11/21/2003, you wrote: Will any names or addresses exceed 255 characters? The documentation recommends to use the smallest column size possible. -will Real-time Chat: irc.freenode.net - #mysql ( http://www.mysql.com/doc/en/IRC.html ) - Original Message - From: Paul Fine [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, November 21, 2003 7:34 AM Subject: Choosing between VARCHAR and TEXT Can anyone tell me what is better to use for items such as names and addresses? I suspect VARCHAR(max anticipated length) but why? Is it because Text will pad? Thanks! -- 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] Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or888-638-6963 Fax: 724-489-4386 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Choosing between VARCHAR and TEXT
One additional consideration would be if you're planning on doing a lot of mailing you should consider having some sort of address validation software. Once you settle on that package, it will also help you determine maximum field/column sizes. After looking at the package(s) you may decide to offer more fields as well. -Original Message- From: William Fong [mailto:[EMAIL PROTECTED] Sent: Friday, November 21, 2003 10:05 AM To: [EMAIL PROTECTED] Subject: Re: Choosing between VARCHAR and TEXT Will any names or addresses exceed 255 characters? The documentation recommends to use the smallest column size possible. -will Real-time Chat: irc.freenode.net - #mysql ( http://www.mysql.com/doc/en/IRC.html ) - Original Message - From: Paul Fine [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, November 21, 2003 7:34 AM Subject: Choosing between VARCHAR and TEXT Can anyone tell me what is better to use for items such as names and addresses? I suspect VARCHAR(max anticipated length) but why? Is it because Text will pad? Thanks! -- 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]
