Re: varchar to text
I think that is absolutely a bad idea. Not only are you splitting data that should logically be together, you will need to add three indexes and perform three searched to get at the data. You typical index only indexes from the start of the field, so even if you split the data, you can't do contained in searches that will use an index. Keep in all in one text field and use a full text index. If you need to index the first 20 characters or something for quick sorting, than also add a regular index on the field. But a full text index on one field is what you want to use. On May 23, 2005, at 2:02 PM, Scott Purcell wrote: After some thought into this, I believe that I may actually need to eventually "search" this field, and may need an index on it. After talking to others at the office, they think that maybe I should try and create three varchar(254) fields and tie them together. Therefore I can have a larger description field, and still be able to index. Is this a bad or crazy idea? I have never heard of doing this, but I can via code, show the results from three tables and concat it together? Any ideas, thoughts I figure I need about avg: 1000 characters per description. Thanks, Scott -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: varchar to text
After some thought into this, I believe that I may actually need to eventually "search" this field, and may need an index on it. After talking to others at the office, they think that maybe I should try and create three varchar(254) fields and tie them together. Therefore I can have a larger description field, and still be able to index. Is this a bad or crazy idea? I have never heard of doing this, but I can via code, show the results from three tables and concat it together? Any ideas, thoughts I figure I need about avg: 1000 characters per description. Thanks, Scott -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, May 23, 2005 8:58 AM To: Brent Baisley Cc: Scott Purcell; mysql@lists.mysql.com Subject: Re: varchar to text Hi, I we can, one should ask if it is a good solution ? text can't be indexed as a varchar. You will need full-text indexing, or a b-tree index on a length-limited : mysql> create index titi on toto2(t); ERROR 1170 (42000): BLOB/TEXT column 't' used in key specification without a key length mysql> create index titi on toto2(t(100)); Query OK, 0 rows affected (0.16 sec) Records: 0 Duplicates: 0 Warnings: 0 Then queries have to changed for using the indexes and so on ... So study carefully before altering the table. Mathias Selon Brent Baisley <[EMAIL PROTECTED]>: > You can safely change varchar to text, since they are the same data > type. > > ALTER TABLE table_name CHANGE field_name field_name TEXT > > But, as always, make sure you have a recent backup. There is always the > possibility that your computer will crash in the middle of the > operation due to a stray galactic neutrino that happens to collide with > a bit of your memory and cause a bit to flip. > > On May 23, 2005, at 9:37 AM, Scott Purcell wrote: > > > Hello, > > I created a table that uses a varchar(254) size field, and found out > > that the customers data is being truncated. It needs to be larger. > > > > Currently there are about 500 records (it is a description field) in > > the column. Upon reading the docs, it looks like I need to use a > > "text" type column. > > > > I have never done an alter with records in a table, specifically when > > it is client-data. How, or where, can I get information on how to > > alter the table to be a text field, or do I need to copy all the data > > to a tmp table, create a new table and copy all the data back to the > > new table with a "text" column? > > > > I have no experience with this area of sql. > > > > Thanks, > > Scott > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > -- > Brent Baisley > Systems Architect > Landover Associates, Inc. > Search & Advisory Services for Advanced Technology Environments > p: 212.759.6400/800.759.0577 > > > -- > 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: varchar to text
Hi, I we can, one should ask if it is a good solution ? text can't be indexed as a varchar. You will need full-text indexing, or a b-tree index on a length-limited : mysql> create index titi on toto2(t); ERROR 1170 (42000): BLOB/TEXT column 't' used in key specification without a key length mysql> create index titi on toto2(t(100)); Query OK, 0 rows affected (0.16 sec) Records: 0 Duplicates: 0 Warnings: 0 Then queries have to changed for using the indexes and so on ... So study carefully before altering the table. Mathias Selon Brent Baisley <[EMAIL PROTECTED]>: > You can safely change varchar to text, since they are the same data > type. > > ALTER TABLE table_name CHANGE field_name field_name TEXT > > But, as always, make sure you have a recent backup. There is always the > possibility that your computer will crash in the middle of the > operation due to a stray galactic neutrino that happens to collide with > a bit of your memory and cause a bit to flip. > > On May 23, 2005, at 9:37 AM, Scott Purcell wrote: > > > Hello, > > I created a table that uses a varchar(254) size field, and found out > > that the customers data is being truncated. It needs to be larger. > > > > Currently there are about 500 records (it is a description field) in > > the column. Upon reading the docs, it looks like I need to use a > > "text" type column. > > > > I have never done an alter with records in a table, specifically when > > it is client-data. How, or where, can I get information on how to > > alter the table to be a text field, or do I need to copy all the data > > to a tmp table, create a new table and copy all the data back to the > > new table with a "text" column? > > > > I have no experience with this area of sql. > > > > Thanks, > > Scott > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > -- > Brent Baisley > Systems Architect > Landover Associates, Inc. > Search & Advisory Services for Advanced Technology Environments > p: 212.759.6400/800.759.0577 > > > -- > 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: varchar to text
You can safely change varchar to text, since they are the same data type. ALTER TABLE table_name CHANGE field_name field_name TEXT But, as always, make sure you have a recent backup. There is always the possibility that your computer will crash in the middle of the operation due to a stray galactic neutrino that happens to collide with a bit of your memory and cause a bit to flip. On May 23, 2005, at 9:37 AM, Scott Purcell wrote: Hello, I created a table that uses a varchar(254) size field, and found out that the customers data is being truncated. It needs to be larger. Currently there are about 500 records (it is a description field) in the column. Upon reading the docs, it looks like I need to use a "text" type column. I have never done an alter with records in a table, specifically when it is client-data. How, or where, can I get information on how to alter the table to be a text field, or do I need to copy all the data to a tmp table, create a new table and copy all the data back to the new table with a "text" column? I have no experience with this area of sql. Thanks, Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: varchar to text
Changing types to larger shouldn't be an issue. If you are changing to smaller, of course you are going to truncate the data more. You do not need to move the data out and then back in again unless you really want to. mysql> ALTER TABLE tbl_name CHANGE `col1_old_name` `col1_new_name` text default null J.R. -Original Message- From: Scott Purcell [mailto:[EMAIL PROTECTED] Sent: Monday, May 23, 2005 9:37 AM To: mysql@lists.mysql.com Subject: varchar to text Hello, I created a table that uses a varchar(254) size field, and found out that the customers data is being truncated. It needs to be larger. Currently there are about 500 records (it is a description field) in the column. Upon reading the docs, it looks like I need to use a "text" type column. I have never done an alter with records in a table, specifically when it is client-data. How, or where, can I get information on how to alter the table to be a text field, or do I need to copy all the data to a tmp table, create a new table and copy all the data back to the new table with a "text" column? I have no experience with this area of sql. Thanks, Scott -- 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]
