Mysql InnoDB engine in Laravel

Question

I am using laravel and laravel migration mechanism. I created tables and seted up foreign keys. But the tables are MyISSAM so no foreign keys are created. Where do I enable / configure this? (to change it to InnoDB and not in the mysql server).

score 128 · Accepted Answer · answered Jun 23 '16 at 15:37

128

You can edit your /config/database.php file, search for mysql entry and change:

'engine' => null,

to

'engine' => 'InnoDB',

This saves you from adding $table->engine = "InnoDB"; for each of your Schemas ;)

answered Jun 23 '16 at 15:37

Thomas LAURENT

1,592
1
11
9

This is perfect! Thank you! – Christoffer Nov 19 '17 at 13:57
1

The question is now, why Laravel allows to create foreign key in the migrations files using MyISAM. Using MySQL 5.7 (so with InnoDB as default), the `migrate` command still creates table with MyISAM, and my foreign keys are only indexes. Weird. – Vincent Decaux Aug 04 '18 at 10:44
2

@VincentDecaux, Laravel has given all the developers the functionality to create the foreign keys via migration code. Suppose if Laravel doesn't allow this functionality and someone, who is using the InnoDB engine, needed it then obviously he'll have to write the query(ies) separately to add foreign key constraints. Which in result will double his/her work and time as well. – Mr.Singh Oct 11 '19 at 07:25

score 14 · Answer 2 · answered Jan 02 '15 at 21:37

14

Define engine like this

  Schema::create("models", function(Blueprint $table) {
            $table->engine = "InnoDB";
  }

answered Jan 02 '15 at 21:37

srsajid

1,717
15
12

score 7 · Answer 3 · edited Oct 21 '16 at 18:54

7

You can set the engine inside Schema\Table closure.

edited Oct 21 '16 at 18:54

Manolis Agkopian

1,033
13
22

answered Jul 07 '12 at 12:34

crynobone

1,814
12
22

Sadly, that link seems to be dead now. – jlbang Jul 17 '15 at 20:53
Now the way to do this is [here](https://laravel.com/docs/5.2/migrations#writing-migrations) in *Connection & Storage Engine* – Thomas LAURENT Jun 23 '16 at 15:18

score 4 · Answer 4 · edited Apr 29 '19 at 13:02

4

Another approach (for whose that don't uses database.php) is to include on .env file:

DB_ENGINE=InnoDB

Remember to check if you have 'engine' => env('DB_ENGINE', null), on your database.php

edited Apr 29 '19 at 13:02

Marcos Rocha

98
8

answered Oct 26 '18 at 12:51

Tiago Gouvêa

15,036
4
75
81

score 4 · Answer 5 · answered Jun 07 '19 at 16:13

I found @ThomasLAURENT is the best solution but what about the existing tables I have in my database.

Working around.

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class ConvertTablesIntoInnoDB extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        $tables = [
            'users',
            'products',
        ];
        foreach ($tables as $table) {
            DB::statement('ALTER TABLE ' . $table . ' ENGINE = InnoDB');
        }
    }
    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        $tables = [
            'users',
            'products',
        ];
        foreach ($tables as $table) {
            DB::statement('ALTER TABLE ' . $table . ' ENGINE = MyISAM');
        }
    }
}

This will allow us to convert all the tables and roll-back them when I need.

score 3 · Answer 6 · edited Feb 22 '20 at 06:58

3

I would recommend to update your Mysql to 5.5 or higher. The default storage engine for Mysql now is InoDB

Before MySQL 5.5.5, MyISAM is the default storage engine. (The default was changed to InnoDB in MySQL 5.5.5.) MyISAM is based on the older (and no longer available) ISAM storage engine but has many useful extensions.

http://dev.mysql.com/doc/refman/5.5/en/myisam-storage-engine.html

Once done, you can easily map relationships within the entity classes via Laravel

edited Feb 22 '20 at 06:58

Pradeep

9,667
13
27
34

answered Jul 06 '12 at 17:30

Akash

4,956
11
42
70

If the tables exist, changing the version to get a different default will not cause the existing tables to change. You need `ALTER TABLE t ENGINE=InnoDB;` (for each `t`). – Rick James Jan 13 '17 at 20:48

Huy Nguyễn · Answer 7 · 2019-11-12T12:13:33.337

2

Schema::create('users', function($table)
{
    $table->engine = 'InnoDB';

    $table->string('email');
});

Like document Laravel: https://laravel.com/docs/4.2/schema#storage-engines

P/s: Thanks @Nico Haase for reminding me to provide the link.

edited Nov 12 '19 at 12:13

answered Mar 14 '19 at 14:31

Huy Nguyễn

27
2

Is there any way for a global definition, such that it works for **all** tables? What's that `email` column used for? – Nico Haase Mar 14 '19 at 15:08
@NicoHaase, that email column is just an example & to answer your question `Yes` you can define it globally. To do that go to `config/database.php` and under `mysql` key change `'engine' => null,` to `'engine' => 'InnoDB',`. – Mr.Singh Oct 11 '19 at 07:30
1

This looks like copy&paste from https://laravel.com/docs/4.2/schema#storage-engines - if you already do this, you should at least provide a link – Nico Haase Oct 11 '19 at 07:35

score 1 · Answer 8 · answered Oct 10 '12 at 15:00

1

Use InnoDb tables on the server side it's best way to success. Use MySQL Workbench. It easy in Workbench. And, if you want read the native manual

answered Oct 10 '12 at 15:00

RDK

4,540
2
20
29

score 0 · Answer 9 · answered Mar 11 '23 at 12:33

0

The best approach for those that don't use database.php) is to include on the .env file: DB_ENGINE=InnoDB. Remember to check if you have 'engine' => env('DB_ENGINE', null) on your database.php

answered Mar 11 '23 at 12:33

Aziz Badar

3
2

Mysql InnoDB engine in Laravel

9 Answers9

Linked