How to create a table with array in mysql

1

Hi,

I am the newbies here.

We are currently doing migration from vision file to maridb.

we are facing some issue is how to create the table contain array field.

For example, we have many SKU, each SKU is having 10 color.
So we want to create the table where having the SKU ID, SKU NAME and color.
where the color is in array that can store 10 color,

Thanks You.

2

so you want to migrate from .txt file?
do you use HeidiSQL or something like that?
which framework do you use?
or you want to save it manually?
tell me more about your position.

3

Hi Jack,

We will using the mariadb & SQL to store the data.
We will using the text file to dump the data from vision file to database.

Now we are having a table where we will need to contain some array in the table.
For example, we have many SKU, each SKU is having 10 color.
So we want to create the table where having the SKU ID, SKU NAME and color.
where the color is in array that can store 10 color,

Thanks You.

4

OK, send me some part of your file which has data.
it should be changed to the type of sql query.
I can help you to make it as query.

5

If the color column is an array you want the type to be JSON.

When you store data in that column you’ll need to JSON.stringify() the data before saving it.

If you are inserting the data with raw sql, you just format the data as valid JSON.

Hope that helps!

6

which programing language do you mastered?
I will send you some logic of algorithm.

  1. stringify the json file.
  2. split it by space,(’ ')
  3. write file like below:
INSERT INTO SKU_table (SKU_id, SKU_name, SKU_color_1, SKU_color_2, ...)
VALUES (value1, value2, value3, ...);

values are read from your file.
best wishes.

7

Hi Danny,

Thanks for your advise.
I had see some others also advise to use JSON, but what i worry is JSON isn’t not same as SQL query we use.

Thanks You.

8

Hi Jack,

We are using COBOL + SQL to access the database.
Appreciate you can give the sample on how to use the json in SQL. so we can have a try.
Because i had try to create using standard SQL way where create the SKU_COLOR_1, SKU_COLOR_2. But we still need to look into the array method, due to we have many data is in array in the same table.

Thanks You.

9

No problem!

I’d have to see some real examples of what you’re talking about to help you further.

10

anyway, you should use any type of programing language to convert Json to Sql.
if you send me some part of your file, i can help you more.

11

Hi Danny,

Thanks for advise.

This is the data structure we have currently.
01 MC-REC.
03 MC-KEY.
05 MC-CT-KEY PIC X(15).
05 MC-CARD-SEQ PIC X(15).
05 MC-SPECIFICATIONS OCCURS 10 TIMES.
07 MC-DESIGN PIC X(10).
07 MC-EXT-MEASUREMENTS.
09 MC-EXT-LENGTH PIC 9(06).
09 MC-EXT-WIDTH PIC 9(06).
09 MC-EXT-HEIGHT PIC 9(06).
07 MC-COLOR OCCURS 08.
09 MC-INK-KEY PIC X(10).
09 MC-INK-PERCENT PIC 9(03)V99.
09 MC-INK-PADDING PIC X(07).

We are using the above data structure and create the table called ‘MC’ and create each field above as a columns. For example MC-SPECIFICATIONS OCCURS 10 TIMES. we will have all the item below this repeat 10 columns.
So total column create based on above data structure will be 300 columns.

Due to we have more than 1 array part of the MC-SPECIFICATIONS array, so when we create the table, we hit the max columns. That why we want to know isn’t the way we use is wrong.

Thanks You.