Best way to store multiple lines of data in MySQL?

Question

I'm looking at storing multiple lines of data in MySQL (or your choice), an example would be a poem. I wouldn't know how many lines it could be and I have to keep its structure intact so when I go display it to my end users on a site it is properly formatted.

I could be storing anywhere between 100 characters to 100,000. I'd rather shy from using plain text unless someone can help me figure out an easier method.

Why would plain text be so bad? – John Conde Aug 20 '12 at 01:16 — John Conde, Aug 20 '12 at 01:16

Jared Drake · Answer 1 · 2012-08-20T01:27:17.363

1

If you're really worried about the formatting. You could also use a content editor that creates html code like <br/> and lists for formatting. Then post that into your sql as a text.

Again, I'm not sure exactly if your worried about inputting data into mysql, or worried about displaying it from mysql (Hence, the content editor idea).

edited Aug 20 '12 at 01:27

answered Aug 20 '12 at 01:22

Jared Drake

982
4
12

Tho in a not so perfect world, you should be careful of XSS, if allowing html tobe added to posts. – Lawrence Cherone Aug 20 '12 at 01:29
Well I'll be implementing most of the text myself so I'll know what goes in for quality control. Would it be wise to store it all in MySQL? What would I store it under? VARCHAR is out of the question based off its 255 size restriction. – Matthew Aug 20 '12 at 01:37
People often store full html documents in sql for the ability to have full db driven content. There is pros and cons to this. It's wise because of how quickly you can modify the data. and Like John said above, storing as text is fine, but be sure to understand sql injection attacks for the future. :) @Matthew – Jared Drake Aug 20 '12 at 01:52

score 1 · Accepted Answer · answered Aug 20 '12 at 02:08

If you store the text in a simple format like Markdown then you can render it as HTML using any number of libraries that convert.

There's nothing wrong with storing large amounts of text in MySQL. For data over 1KB you should be using TEXT for up to 64KB or LONG TEXT for much larger amounts.

VARCHAR can go to longer lengths under newer versions of MySQL but it's probably not what you intend here.

Best way to store multiple lines of data in MySQL?

2 Answers2