MySQL group_concat and Postgres array_agg

MySQL group_concat and Postgres array_agg

Mysql

I recently discovered a cool function in MySQL that is nice for doing group by queries where you want to return a column with a comma separated list of data to sum up totals. I learn best from examples so let’s look a 3 table example.

Products

CREATE TABLE products (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(255) NOT NULL,
  PRIMARY KEY (id)
)

Inventory Items

CREATE TABLE inventory_items (
  id int(11) NOT NULL AUTO_INCREMENT,
  product_id int(11) NOT NULL,
  sku varchar(255) NOT NULL,
  PRIMARY KEY (id)
)

Orders

CREATE TABLE orders (
  id SERIAL,
  inventory_item_id INTEGER NOT NULL,
  amount INTEGER NOT NULL,
  PRIMARY KEY (id)
)

Read More

MySQL SOURCE command for importing large SQL files

MySQL SOURCE command for importing large SQL files

If you have a large database that you frequently dump and rebuild, you might want to consider using the mysql SOURCE command. In the past, with smaller databases, I have imported using the following method:

mysql -u root -p monkseal_development < monkseal_development.sql

However, if monkseal_development.sql is large, there’s a way to speed this up.

mysql --max_allowed_packet=128M -u root -p monkseal_development
....

mysql> SOURCE monkseal_development.sql

Read More