How can I insert records into MySql table so that one cell has multiple values?

Question

Sorry if the title is confusing, here's what I want to do:

INSERT INTO table (fruit, type) VALUES 
('apple', '1'), 
('apple', '2'), 
('apple', '2'), 
('banana', '3'), 
('banana', '4'), 
('banana', '5'); 

So that the resulting table only has two rows with pipe-separated values in the type column that look like this:

('apple', '1|2') 
('banana', '3|4|5')  

Right now I have:

CREATE TABLE IF NOT EXISTS table ( 
id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  fruit varchar(50) NOT NULL UNIQUE KEY, 
  type varchar(100) NOT NULL 
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;  

INSERT INTO table (fruit, type) VALUES 
('apple', '1'), 
('apple', '2'), 
('apple', '2'), 
('banana', '3'), 
('banana', '4'), 
('banana', '5') 
ON KEY DUPLICATE type = VALUES(type)|type 
//I know above line is wrong and this is where I'm stuck 

Is it possible for me to get a series of pipe-separated values in the type column?

Many thanks

EDIT:

I got almost where I want to end up by doing this:

CREATE TABLE IF NOT EXISTS table ( 
id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  fruit varchar(50) NOT NULL UNIQUE KEY, 
  type varchar(100) NOT NULL 
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;  

INSERT INTO table (fruit, type) VALUES 
('apple', '1'), 
('apple', '2'), 
('apple', '2'), 
('banana', '3'), 
('banana', '4'), 
('banana', '5') 
ON KEY DUPLICATE UPDATE type = CONCAT(type, '|', VALUES(type))

The only issue is that the resulting rows are:

('apple', '1|2|2')
('banana', '3|4|5')

I'm trying out different methods of DISTINCT to get rid of that last '2' but I keep getting MySql errors

Answer 1

MySQL's command is called ON DUPLICATE KEY UPDATE

And if you want to keep it in the order you show (like ('banana', '3|4|5')) you should use it like this:

ON DUPLICATE KEY UPDATE type = CONCAT(type, '|', VALUES(type))

since VALUES refers to the new value that is trying to be inserted on the existing record.

If you want to avoid repeated values, try with:

ON DUPLICATE KEY UPDATE type = IF(type LIKE CONCAT('%', VALUES(type), '%'), type, CONCAT(type, '|', VALUES(type)) ) 

I agree that this is not the best way to handle a DB, but if this is what you want, this should do the trick.

From: https://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Answer 2

Insert of inserting data with pipe separation, you can insert data into the database normally like so:

create table test(id int, fruit varchar(50), type varchar(100));
INSERT INTO test (fruit, type) VALUES 
('apple', '1'), 
('apple', '2'), 
('apple', '2'), 
('banana', '3'), 
('banana', '4'), 
('banana', '5');

Then write a query like this to extract data in the manner you want:

select fruit, group_concat(distinct type separator '|') as type
from test
group by fruit

Result:
fruit   type
apple   1|2
banana  3|4|5

SQLFiddle Example: http://sqlfiddle.com/#!9/90a9b/1

You can even create a view like this:

create view test_pipedelimited as 
select fruit, group_concat(distinct type separator '|') as type
from test
group by fruit;

And then always just use to get results in pipe-delimited format

select * from test_pipedelimited

Procedure

If you really, really want to store type with pipe-delimitation, you could do that through a stored procedure like this:

delimiter //
create procedure insert_data_into_test (
    p_fruit varchar(50),  -- takes fruit as first argument
    p_type varchar(100),  -- takes type as second argument
    OUT p_id int -- populates this variable with -1 if it was an update or with last inserted ID if it was an insert
)
begin

   -- find out if the fruit is already in the table
    declare fruit_count int;
    select count(*) into fruit_count from test where fruit = p_fruit;

    -- if fruit is in the table, do an update
    if fruit_count > 0 then
        update test set type = concat(type, '|', p_type) where fruit = p_fruit;
        set p_id = -1;

    -- if fruit is NOT in the table, do an insert
    else
        insert into test (fruit, type) values (p_fruit, p_type);
        select last_insert_id() into p_id;

    end if; 

end; //
delimiter ;

How do you call this procedure?

create table test (id int primary key auto_increment, fruit varchar(50), type varchar(100));

-- create the procedure here, technically

call insert_data_into_test('apple', '1', @id);
select @id;
Result: 1

select * from test;
ID fruit  type
1  apple  1

call insert_data_into_test('apple', '2', @id);
select @id;
Result: -1

select * from test;
ID fruit  type
1  apple  1|2

call insert_data_into_test('toast', '3', @id);
select @id;
Result: 2

select * from test;
ID fruit  type
1  apple  1|2
2  toast  3

Recommendation

I highly recommend not storing data like that in a database. True power of the database will be realized by storing data methodically and following some known normalization rules. This technique violates the first rule of normalization (1NF).

It will also limit you when you want to avoid inserting duplicate types. Let's say you enter apple and 1 and you do that again. You will get type of 1|1. To prevent that, you would have to go do much more work to find out if 1 is present in type. You cannot just do LIKE type = '%1%' because that would match 1, 11, 21 etc. You can't sanely find out max type for each fruit etc.

Better procedure

delimiter //
create procedure insert_data_into_test (
    p_fruit varchar(50),  -- takes fruit as first argument
    p_type varchar(100),  -- takes type as second argument
    OUT p_id int -- populates this variable with -1 if it was an update or with last inserted ID if it was an insert
)
begin

   -- find out if the fruit is already in the table
    declare fruit_count int;
    declare fruit_type_count int;
    select count(*) into fruit_count from test where fruit = p_fruit;

    -- if fruit is in the table, do an update
    if fruit_count > 0 then

        -- let's check if the the type is already there for the fruit       
        -- we don't want to put duplicates in
        select count(*) into fruit_type_count 
        from test 
        where 
            fruit = p_fruit 
            and (                                                               -- if p_type was 2
                    type regexp concat('^', p_type, '$')            -- ^2$ regex matches if type starts and ends with 2
                    or type regexp concat('^', p_type, '\\|')   -- ^2| regex matches if type starts with 2| (the \\ is to prevent MySQL from giving a special meaning to |)
                    or type regexp concat('\\|', p_type, '\\|')     -- |2| regex matches if type is in the middle somewhere
                    or type regexp concat('\\|', p_type, '$')       -- |2$ regex matches if type is at the end but there's something before it
                );

        -- only update type if the type is not already present      
        if fruit_type_count = 0 then    
            update test set type = concat(type, '|', p_type);
        end if;
        set p_id = -1;

    -- if fruit is NOT in the table, do an insert
    else
        insert into test (fruit, type) values (p_fruit, p_type);
        select last_insert_id() into p_id;

    end if; 

end; //
delimiter ;

This better procedure accounts for duplicates and doesn't insert a duplicate.