Multiple Newline(s) Get Removed During Insert

[peterlaursen]

My first thought was that this is basically how a GUI editor is supposed to work!

a SPACE, a TAB or a NEWLINE (or hundreds of them!) are considered totally having the same meaning!

If there is more than one only the first is kept and the rest are stripped away –  SPACES, TABS and NEWLINES are considered a matter of SQL code formatting for the display only.

But well . . you got a point: inside a quoted string it should not do like this!   So I will agree that this is a bug! As we accept one linebreak inside the quoted string we should accept all of them (as we do accept multiple TABS and multiple SPACES inside a quoted string and do not strip any of those)

The workaround is to add an additional 'newline' as a C-style escape sequence “n” like

Code:

drop table if exists test;

create table test (
id int not null auto_increment primary key,
some text not null
) engine = InnoDB;

insert into test (some) values ('

one newline character:
two newline characters:n

end of story

');

select *
from test;

etc.

and of course this

Code:

one newline character:
two newline characters:n

end of story

and this

Code:

one newline character:
two newline characters:n
end of story

would be exactly the same!

But basically I would avoid using visual linebreaks in the quoted string itself but do like:

Code:

insert into test (some)
values ('one newline character:ntwo newline characters:nnend of story');

.. because that would work with any client – whether a graphical client as SQlyog or a commandline or a script/an application! In a similar way I would write “t” instead of a visual TAB.

Actually there could be a platform related issue too.  On Linux 'newline' is plain “n” (or ins ASCII/ANSI terminologi) – on windows it is “rn” ().  Typing/sending the escape sequences instead of relying on the visual display in sosme 'accidential program' is the safe way.  

So it could also be

Code:

insert into test (some)
values ('one newline character:rntwo newline characters:rnrnend of story');

. and this one I think I would actually prefer as not all Windows programs understand the unix/linux way of doing things.  One program that does not is Notepad!  I have not come across any *nix program having problems with display of Windows text!

[Author]

Posts