CODEWITHSUNDEEP
phpmysql
LyleCrumbstorm
LyleCrumbstorm

Reputation: 171

How to store many rows of long strings in mySQL

I need to store 100 URL addresses + 100 text labels. I would like to have a default value for both. The problem that I have run into is that I have set the URL fields as VARCHAR, length = 1024 and the text labels as VARCHAR, length = 30. while building my table I've encountered the error, "Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs"

How else might I go about accomplishing my objective? I can't use TEXT b/c it does not allow me to store a default value for that field.

Upvotes: 0

Views: 1018

Answers (2)

Will Hartung
Will Hartung

Reputation: 118641

Having 100 fields in a record for this is, bluntly, wrong. Don't do that.

Instead make a new table with it's own primary and a foreign key linking back to the parent table, and each of the rows in this table can hold the URL and label. Then you can have your application ensure that no more than 100 rows per parent are stored in this table.

Yea, the idiom is quite different from what you have now, but it's much, much better.

Edit for comment:

SQL best models "collections of things" as rows in tables. The classic discussion point in a database model is when you're talking about a specific piece of data. The question inevitably boils down to "is there going to be 1 of these items, or many".

If there is just one item, then this best modeled as a field in a row in a table. If there are many, then these items are best modeled as individual rows in a table.

Right now you may have something like:

create table user {
    id number primary key not null,
    firstName varchar(30),
    lastName varchar(30),
    url1 varchar(1024),
    url1label varchar(30),
    url2 varchar(1024),
    url2label varchar(30),
    url3 varchar(1024),
    url3label varchar(30),
    url4 varchar(1024),
    url4label varchar(30)
}

Having repeating fields like this is a "bad" pattern in SQL databases, as a rule. All rules have exception, but as a general rule, when just starting out, it's a bad idea. At a minimum you're taking up space for all of these url fields, no matter how many they actually use.

Second, from a SQL point of view, these numerous, repeated fields are extremely difficult to work with. For example, you can't easily query the database to see if anyone has http://google.com as a url. You would have to have something awful like:

select * from user where url1 = "http://google.com" or url2 = "http://google.com" ...

So, rather a better model would be something like:

create table user {
    id number primary key not null,
    firstName varchar(30),
    lastName varchar(30),
}

create table urls {
    id number primary key not null,
    user_id number not null references user(id),
    url varchar(1024),
    label varchar(30)
}

Here, each url row has it's own primary key, and a reference to the user that the url belongs to via user_id.

If you want to get all of the urls for a user you would do:

select * from urls where user_id = 123

Now, the database offers no limitation to how many urls a user can hold. A user can have 0, or 1, or a million. The database will not enforce any kind of limit here. If you want to limit them to 100 urls, then your application will need to do that itself.

But hopefully you can see that if one user has 2 urls, they will have only 2 rows in the urls table, whereas a user with 50 urls will have 50 rows.

For you other fields that you only have 1 of (like first name and last name) those could all be fields on the primary user table.

But fields that repeat, most likely, are better represented as their own table with a foreign key to the parent

Upvotes: 3

ioan
ioan

Reputation: 771

Is okey having a fields varchar of length 1024 and another one of length 30. Proof: http://sqlfiddle.com/#!2/8d4f7/1024

As a warkaround at your issue, you can use TEXT (which is recomanded probably for better storage?).

But, the problem is that the total length of your row table must not be greater than 65532.

References: http://www.pythian.com/blog/text-vs-varchar/

Upvotes: 0

Related Questions