Reputation: 6233

What datatype to use in a MySQL-database when inserting comma-seperated int numbers?

I got one general and probably easy question. When saving an array of int numbers into a MySQL-database (numbers are seperated by a comma), what would be the correct datatype for the database to choose? When choosing INT as type, I guess I may not use commas, may I? So I used "text" as datatype since these arrays of numbers can get really long and I didnt want to set a limit. But is this the right way to go or is it bad to save only numbers with comma in a "text" field in my database?

Thanks for your help! phpheini

Upvotes: 0

Answers (5)

Conrad Frix

Reputation: 52645

The best datatype would be another table.

It is indeed very "bad to save only numbers with comma in a "text" field in my database"

This would be breaking First Normal Form

Doing this will cause you increase data contention on the field when modifying values.

For example lets suppose two users want to add a new value to the list of numbers associated with a record. When the table is in 1NF normally both users would get to insert a record. When not in 1NF its easy for one user to lose the addition because you're writing back several values to a single attribute.

It also makes it very difficult to do simple kinds of queries like (which records have a 3 in the list). Yes it can be done but it won't be able to use any indexes, because databases don't index what's inside a field (except for special cases like SQL Server's XML type)

Upvotes: 2

Punit

Reputation: 1120

it would be batter to serialize the array and than store it to database and while accessing unrealize that, you can use as varchar/longtext as datatype depends how much data your array has. you will see the difference in below example.

$var  = array('1'=>'a','2'=>'b');
$var2  = serialize($var);
echo $var2;
print_r(unserialize($var2));

Upvotes: 1

Benjam

Reputation: 5316

Text would be my suggestion, but it comes with the caveat that it's not easily searchable or indexable by a particular value in that list of values.

Without knowing what those values are and what they are for, I can't suggest anything else, but you might want to consider database normalization. It's far better in the long run.

Upvotes: 0