title: MySQL: Split comma separated list into multiple rows
body: <p>I have an unnormalized table with a column containing a comma separated list that is a foreign key to another table:</p>
  
  <pre><code>+----------+-------------+   +--------------+-------+
  | part_id  | material    |   | material_id  | name  |
  +----------+-------------+   +--------------+-------+
  |      339 | 1.2mm;1.6mm |   |            1 | 1.2mm |
  |      970 | 1.6mm       |   |            2 | 1.6mm |
  +----------+-------------+   +--------------+-------+
  </code></pre>
  
  <p>I want to read this data into a search engine that offers no procedural language.</p>
  
  <p>So is there a way to <strong>either</strong> make a join on this column <strong>or</strong> run a query on this data that inserts appropriate entries into a new table?
  The resulting data should look like this:</p>
  
  <pre><code>+---------+-------------+
  | part_id | material_id |
  +---------+-------------+
  |     339 |           1 |
  |     339 |           2 |
  |     970 |           2 |
  +---------+-------------+
  </code></pre>
  
  <p>I could think of a solution if the DBMS supported functions returning a table but MySQL apparently doesn't.</p>