String insertion
Hi! I just hit something really strange which is either a bug in MySQL or a dumbness bug of me. I am trying to insert a string ending with a simple space and i really want this space at the end of my string ;). Unfortunately MySQL kills this whitespace when inserting into normal (var)char columns: mysql create table test_strings (foo_1 varchar(255),foo_2 char(255),foo_3 text,foo_4 blob); Query OK, 0 rows affected (0.00 sec) mysql insert into test_strings set foo_1=' test ',foo_2=' test ',foo_3=' test ',foo_4=' test '; Query OK, 1 row affected (0.00 sec) mysql select length(foo_1),length(foo_2),length(foo_3),length(foo_4) from test_strings; +---+---+---+---+ | length(foo_1) | length(foo_2) | length(foo_3) | length(foo_4) | +---+---+---+---+ | 5 | 5 | 6 | 6 | +---+---+---+---+ 1 row in set (0.00 sec) Is this a feature or am i missing something. text would do it for me but it is a total waste of space. Any ideas? Kind regards, Andreas Streichardt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: String insertion
Andreas Steichardt [EMAIL PROTECTED] wrote on 24/10/2005 10:34:08: Hi! I just hit something really strange which is either a bug in MySQL or a dumbness bug of me. I am trying to insert a string ending with a simple space and i really want this space at the end of my string ;). Unfortunately MySQL kills this whitespace when inserting into normal (var)char columns: mysql create table test_strings (foo_1 varchar(255),foo_2 char(255),foo_3 text,foo_4 blob); Query OK, 0 rows affected (0.00 sec) mysql insert into test_strings set foo_1=' test ',foo_2=' test ', foo_3=' test ',foo_4=' test '; Query OK, 1 row affected (0.00 sec) mysql select length(foo_1),length(foo_2),length(foo_3),length(foo_4) from test_strings; +---+---+---+---+ | length(foo_1) | length(foo_2) | length(foo_3) | length(foo_4) | +---+---+---+---+ | 5 | 5 | 6 | 6 | +---+---+---+---+ 1 row in set (0.00 sec) Is this a feature or am i missing something. text would do it for mebut it is a total waste of space. Any ideas? This is a feature of VARCHAR in MySQL V4 and before. It is fixed in 5.0.3. The Manual ( http://dev.mysql.com/doc/refman/5.0/en/char.html ) suggests using BLOB or TEXT instead of VARCHAR to avoid this behaviour in earlier versions. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: String insertion
Can you force it to keep the space by escaping the space? Something like: mysql insert into test_strings set foo_1=' test\ ',foo_2=' test\ ',foo_3=' test\ ',foo_4=' test\ '; On Oct 24, 2005, at 5:34 AM, Andreas Steichardt wrote: Hi! I just hit something really strange which is either a bug in MySQL or a dumbness bug of me. I am trying to insert a string ending with a simple space and i really want this space at the end of my string ;). Unfortunately MySQL kills this whitespace when inserting into normal (var)char columns: mysql create table test_strings (foo_1 varchar(255),foo_2 char(255),foo_3 text,foo_4 blob); Query OK, 0 rows affected (0.00 sec) mysql insert into test_strings set foo_1=' test ',foo_2=' test ',foo_3=' test ',foo_4=' test '; Query OK, 1 row affected (0.00 sec) mysql select length(foo_1),length(foo_2),length(foo_3),length(foo_4) from test_strings; +---+---+---+---+ | length(foo_1) | length(foo_2) | length(foo_3) | length(foo_4) | +---+---+---+---+ | 5 | 5 | 6 | 6 | +---+---+---+---+ 1 row in set (0.00 sec) Is this a feature or am i missing something. text would do it for me but it is a total waste of space. Any ideas? Kind regards, Andreas Streichardt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Bruce Martin The Martin Solution PO Box 644 Delaware Water Gap, PA (570) 421-0670 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: String insertion
On Monday 24 October 2005 12:00, Bruce Martin wrote: Can you force it to keep the space by escaping the space? Something like: mysql insert into test_strings set foo_1=' test\ ',foo_2=' test\ ',foo_3=' test\ ',foo_4=' test\ '; no...doesn't work...guess i will have to use text :| Kind regards, Andreas Streichardt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]