基于 yiisoft/yii2-app-advanced，在 GitHub 上新建仓库 yii2-app-advanced，新建接口应用(实现 RESTful 风格的 Web Service 服务的 API)，调整默认字符集为：utf8mb4，接口响应格式的调整，空数组自动转换为空对象，在接口应用中收集请求日志消息(1个请求对应1条日志消息)至数据库，且实现日志功能的相应接口：日志列表(设置数据过滤器以启用筛选器处理)、日志详情 (五) (2)

1、在开发环境中，执行数据库迁移命令失败：1071 Specified key was too long; max key length is 767 bytes，如图17

图17

[root@45fdb670c7c4 /]# php /sobey/www/pcs-api/yii migrate --interactive=0
Yii Migration Tool (based on Yii v2.0.15.1)

Total 1 new migration to be applied:
        m180620_105204_update_table_options_to_log

*** applying m180620_105204_update_table_options_to_log
    > execute SQL: ALTER TABLE {{%user}} CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ...Exception: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes
The SQL being executed was: ALTER TABLE `pa_user` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci (/sobey/www/pcs-api/vendor/yiisoft/yii2/db/Schema.php:664)
#0 /sobey/www/pcs-api/vendor/yiisoft/yii2/db/Command.php(1263): yii\db\Schema->convertException(Object(PDOException), 'ALTER TABLE `pa...')
#1 /sobey/www/pcs-api/vendor/yiisoft/yii2/db/Command.php(1075): yii\db\Command->internalExecute('ALTER TABLE `pa...')
#2 /sobey/www/pcs-api/vendor/yiisoft/yii2/db/Migration.php(219): yii\db\Command->execute()
#3 /sobey/www/pcs-api/console/migrations/m180620_105204_update_table_options_to_log.php(19): yii\db\Migration->execute('ALTER TABLE {{%...')
#4 /sobey/www/pcs-api/vendor/yiisoft/yii2/db/Migration.php(114): m180620_105204_update_table_options_to_log->safeUp()
#5 /sobey/www/pcs-api/vendor/yiisoft/yii2/console/controllers/BaseMigrateController.php(725): yii\db\Migration->up()
#6 /sobey/www/pcs-api/vendor/yiisoft/yii2/console/controllers/BaseMigrateController.php(199): yii\console\controllers\BaseMigrateController->migrateUp('m180620_105204_...')
#7 [internal function]: yii\console\controllers\BaseMigrateController->actionUp(0)
#8 /sobey/www/pcs-api/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#9 /sobey/www/pcs-api/vendor/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#10 /sobey/www/pcs-api/vendor/yiisoft/yii2/console/Controller.php(148): yii\base\Controller->runAction('', Array)
#11 /sobey/www/pcs-api/vendor/yiisoft/yii2/base/Module.php(528): yii\console\Controller->runAction('', Array)
#12 /sobey/www/pcs-api/vendor/yiisoft/yii2/console/Application.php(180): yii\base\Module->runAction('migrate', Array)
#13 /sobey/www/pcs-api/vendor/yiisoft/yii2/console/Application.php(147): yii\console\Application->runAction('migrate', Array)
#14 /sobey/www/pcs-api/vendor/yiisoft/yii2/base/Application.php(386): yii\console\Application->handleRequest(Object(yii\console\Request))
#15 /sobey/www/pcs-api/yii(23): yii\base\Application->run()
#16 {main}
*** failed to apply m180620_105204_update_table_options_to_log (time: 0.008s)


0 from 1 migrations were applied.

Migration failed. The rest of the migrations are canceled.

2、在开发环境中的数据库中运行SQL，报错：#1071 – Specified key was too long; max key length is 767 bytes，如图18

图18

3、如果使用的是utf8mb4，并且在长度超过191个字符的varchar列上有唯一索引，则需要打开innodb_large_prefix以允许索引中的较大列，因为utf8mb4需要比utf8或latin1更多的存储空间。如果启用innodb_large_prefix（默认值），则对于使用DYNAMIC或COMPRESSED行格式的InnoDB表，索引键前缀限制为3072个字节。如果innodb_large_prefix被禁用，则任何行格式的表的索引键前缀限制为767字节。在本地环境执行迁移命令成功，是因为本地环境的MySQL版本为5.7.19(MySQL 5.7.7 默认启用innodb_large_prefix，Innodb_large_prefix在MySQL 5.7.7中已弃用，并将在未来版本中删除。)，而开发环境为 5.6.16，查看网址：https://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html ，如图19

图19

4、阿里云RDS的参数配置，启用Innodb_large_prefix，如图20

图20

5、在开发环境中的数据库中运行SQL，成功，如图21

图21

ALTER DATABASE `pcs-api-dev` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci
ALTER TABLE pa_user ROW_FORMAT=DYNAMIC
ALTER TABLE pa_user CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

注：一些数据库还可能需要运行

set global innodb_file_format = BARRACUDA;

6、编辑数据库迁移文件，\console\migrations\m180620_105204_update_table_options_to_log.php

<?php

use yii\db\Migration;

/**
 * Class m180620_105204_update_table_options_to_log
 */
class m180620_105204_update_table_options_to_log extends Migration
{
    /**
     * {@inheritdoc}
     */
    public function safeUp()
    {
        $tableOptions = null;
        if ($this->db->driverName === 'mysql') {
            // http://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci
            $tableOptions = 'CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB';
            $this->execute('ALTER TABLE {{%user}} ROW_FORMAT=DYNAMIC');
            $this->execute('ALTER TABLE {{%user}} CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci');
            $this->execute('ALTER TABLE {{%log}} ROW_FORMAT=DYNAMIC');
            $this->execute('ALTER TABLE {{%log}} CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci');
        }

        $this->addCommentOnTable('{{%user}}', '用户', $tableOptions);
        $this->addCommentOnTable('{{%log}}', '日志', $tableOptions);
    }

    /**
     * {@inheritdoc}
     */
    public function safeDown()
    {
        echo "m180620_105204_update_table_options_to_log cannot be reverted.\n";

        return false;
    }

    /*
    // Use up()/down() to run migration code without a transaction.
    public function up()
    {

    }

    public function down()
    {
        echo "m180620_105204_update_table_options_to_log cannot be reverted.\n";

        return false;
    }
    */
}

7、配置请求日志允许记录的请求方法，主要是忽略 GET 请求的日志，编辑 \api\config\params.php

<?php
return [
    'adminEmail' => 'admin@example.com',
    'requestLog' => [
        'allowMethod' => ['POST', 'PUT', 'DELETE'], //请求日志允许记录的请求方法
    ],
];

8、在 Postman 中，GET http://api.pcs-api.localhost/v1/logs?login_id=e56db1b43546a110431ac38409ed8e9e&login_tid=49117dc410c491af0de08f2948aecd8f&page=2

{
    "code": 10000,
    "message": "获取日志列表成功",
    "data": {
        "items": [
            {
                "id": 21,
                "level": 4,
                "category": "api\\behaviors\\RequestLogBehavior::afterRequest",
                "log_time": 1530000329.6175,
                "prefix": "[app-api][/v1/config-column-users/1][8]",
                "message": {
                    "url": "/v1/config-column-users/1",
                    "request_query_params": {
                        "id": "1",
                        "login_id": "e56db1b43546a110431ac38409ed8e9e",
                        "login_tid": "49117dc410c491af0de08f2948aecd8f"
                    },
                    "request_body_params": {
                        "users": [
                            {
                                "user_pic": "https://cmcconsole.chinamcloud.com/user_pic/1800000005_13281105967_1529552309.png",
                                "user_mobile": "13281105967",
                                "id": "8",
                                "user_nick": "13281105967",
                                "group_id": "015ce30b116ce86058fa6ab4fea4ac63",
                                "user_type": "1",
                                "update_time": "2018-06-26 15:37:52",
                                "add_time": "2018-04-26 10:05:28",
                                "login_name": "13281105967",
                                "user_token": "fb46626f0e71e423ca8ab4c750620a85",
                                "is_open": "1",
                                "user_email": "13281105967@chinamcloud.com"
                            },
                            {
                                "id": "299",
                                "group_id": "015ce30b116ce86058fa6ab4fea4ac63",
                                "login_name": "test11",
                                "user_token": "670d30e8d2d1f994fc0788d4ce95e0f3",
                                "user_nick": "test11",
                                "user_pic": "https://cmcconsole.chinamcloud.com/imgs/default_header.png",
                                "user_mobile": "",
                                "user_email": "",
                                "is_open": "1",
                                "add_time": "2018-05-15 18:47:43",
                                "update_time": "2018-05-18 15:08:11",
                                "user_type": "2"
                            }
                        ]
                    },
                    "user_id": "8",
                    "$_SERVER": {
                        "HTTP_ACCEPT_LANGUAGE": "zh-CN,zh;q=0.9",
                        "HTTP_ACCEPT": "application/json; version=0.0; cookie=enable",
                        "HTTP_HOST": "api.pcs-api.localhost",
                        "REMOTE_ADDR": "127.0.0.1",
                        "REQUEST_URI": "/v1/config-column-users/1",
                        "REQUEST_METHOD": "PUT",
                        "CONTENT_TYPE": "application/x-www-form-urlencoded; charset=utf-8"
                    }
                }
            },
            {
                "id": 22,
                "level": 4,
                "category": "api\\behaviors\\RequestLogBehavior::afterRequest",
                "log_time": 1530000396.3784,
                "prefix": "[app-api][/v1/config-columns][8]",
                "message": {
                    "url": "/v1/config-columns",
                    "request_query_params": {
                        "login_id": "e56db1b43546a110431ac38409ed8e9e",
                        "login_tid": "49117dc410c491af0de08f2948aecd8f"
                    },
                    "request_body_params": {
                        "status": "1",
                        "code": "wxbj",
                        "name": "无线北京"
                    },
                    "user_id": "8",
                    "$_SERVER": {
                        "HTTP_ACCEPT_LANGUAGE": "zh-CN,zh;q=0.9",
                        "HTTP_ACCEPT": "application/json; version=0.0; cookie=enable",
                        "HTTP_HOST": "api.pcs-api.localhost",
                        "REMOTE_ADDR": "127.0.0.1",
                        "REQUEST_URI": "/v1/config-columns",
                        "REQUEST_METHOD": "POST",
                        "CONTENT_TYPE": "application/x-www-form-urlencoded; charset=utf-8"
                    }
                }
            },
            {
                "id": 23,
                "level": 4,
                "category": "api\\behaviors\\RequestLogBehavior::afterRequest",
                "log_time": 1530000418.5957,
                "prefix": "[app-api][/v1/config-columns/3][8]",
                "message": {
                    "url": "/v1/config-columns/3",
                    "request_query_params": {
                        "id": "3",
                        "login_id": "e56db1b43546a110431ac38409ed8e9e",
                        "login_tid": "49117dc410c491af0de08f2948aecd8f"
                    },
                    "request_body_params": {},
                    "user_id": "8",
                    "$_SERVER": {
                        "HTTP_ACCEPT_LANGUAGE": "zh-CN,zh;q=0.9",
                        "HTTP_ACCEPT": "application/json; version=0.0; cookie=enable",
                        "HTTP_HOST": "api.pcs-api.localhost",
                        "REMOTE_ADDR": "127.0.0.1",
                        "REQUEST_URI": "/v1/config-columns/3",
                        "REQUEST_METHOD": "DELETE",
                        "CONTENT_TYPE": "application/json; charset=utf-8"
                    }
                }
            }
        ],
        "_links": {
            "self": {
                "href": "http://api.pcs-api.localhost/v1/logs?login_id=e56db1b43546a110431ac38409ed8e9e&login_tid=49117dc410c491af0de08f2948aecd8f&page=2"
            },
            "first": {
                "href": "http://api.pcs-api.localhost/v1/logs?login_id=e56db1b43546a110431ac38409ed8e9e&login_tid=49117dc410c491af0de08f2948aecd8f&page=1"
            },
            "prev": {
                "href": "http://api.pcs-api.localhost/v1/logs?login_id=e56db1b43546a110431ac38409ed8e9e&login_tid=49117dc410c491af0de08f2948aecd8f&page=1"
            }
        },
        "_meta": {
            "totalCount": 23,
            "pageCount": 2,
            "currentPage": 2,
            "perPage": 20
        }
    }
}