How to make list in schema using MySQL as database?

Questions / Help
1

While working with ecto schema connecting to Mysql as database I tried using list data type. I used {:array, :inner_type} for storing list into the database as per hex docs info https://hexdocs.pm/ecto/Ecto.Schema.html .but it shows an error that MySQL doesn’t support array, so is there any solution?

2

Mysql doesn’t support list, that documentation is for postgresql I think. You can create an ecto custom type which will encode array as json and then save as string to mysql.

3

Some of ecto features are only supported by some adapters - MySQL in general does not support more advanced features, arrays are one of them (this is not a limitation of Ecto, but of MySQL). There are several options of emulating them - either serialising to JSON or simple comma separated string, or even having a separate table.

2 Likes
4

I had the same problem.
Decided to use an old plain one-to-many association instead. it’s a SQL database in the end.

1 Like
5

Thanks for your replies it is very helpful

7

As a supplement at 2021…

Ecto v3 with MySQL < 5.7.8 (don’t support JSON) :

  • use :text in migrations.
  • use :map or {:array, inner_type} at schema level, and Ecto will encode and decode it for you.

Ecto v3 with MySQL >= 5.7.8:

  • use :map or :json in migrations.
  • use :map or {:array, inner_type} at schema level.
3 Likes
8

I am using the :map in migration and in schema field :filed_name, {:array, :integer}
But when i query the table I am getting the below error
cannot load “[1,3,23]” as type {:array, :integer} for field :filed_name

is there anything i am doing wrong?

9

What is the version of MySQL that you are using?