2020年9月

CREATE DATABASE jira CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
给innodb数据表的varchar字段添加索引时, 不得超过 191字符;

解决:


MySQL的InnoDB存储引擎的表存在一系列的限制条件,其中比较常见的一种是表的字段索引长度限制,该限制与参数innodb_large_prefix相关。

问题现象

执行如下两个建表SQL操作,都返回了报错信息,导致建表失败:

报错信息:ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
报错信息:ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

原因分析

导致上面报错的原因是由于InnoDB表的索引长度限制,在MySQL5.6版本后引入了参数innodb_large_prefix可以解决这个问题。该参数控制是否允许单列的索引长度超过767字节,有ON和OFF两个取值:

ON :Innodb表的行记录格式是Dynamic或Compressed的前提下,单列索引长度上限扩展到3072个字节
OFF:Innodb表的单例索引长度最多为767个字节,索引长度超出后,主键索引会创建失败,辅助索引会被截断成为前缀索引

解决方案

  1. 使用前缀索引,减少字段索引长度至767 bytes,即varchar(191)
  2. 或者, 设置MySQL的全局参数innodb_large_prefix=ON,将InnoDB表的索引长度上限扩大到3072个字节

主机名不允许的时候, 要注释掉相关头部

    location ^~ /abc {
            proxy_http_version 1.1;
            proxy_set_header Connection "keep-alive";
            proxy_redirect     off;
  #          proxy_set_header   Host             $host;
            proxy_set_header   X-Real-IP        $remote_addr;
            proxy_set_header   X-Forwarded-For  $proxy_add_x_forwarded_for;
            proxy_next_upstream error timeout invalid_header http_500 http_502 http_503 http_504;
            proxy_max_temp_file_size 0;
            proxy_connect_timeout      90;
            proxy_send_timeout         3600;
            proxy_read_timeout         3600;
            proxy_buffer_size          4k;
            proxy_buffers              4 32k;
            proxy_busy_buffers_size    64k;
            proxy_temp_file_write_size 64k;

        proxy_pass https://aaaa.zdb.im;
    }

function getQueryStringObj() {
        var url = location.search; //获取url中"?"符后的字串
        var theRequest = new Object();
        if (url.indexOf("?") != -1) {
            var str = url.substr(1);
            strs = str.split("&");
            for (var i = 0; i < strs.length; i++) {
                theRequest[strs[i].split("=")[0]] = unescape(strs[i].split("=")[1]);
            }
        }
        return theRequest;
}

function getQueryString(name) {
        var reg = new RegExp('(^|&)' + name + '=([^&]*)(&|$)', 'i');
        var r = window.location.search.substr(1).match(reg);
        if (r != null) {
            return unescape(r[2]);
        }
        return null;
}

function parseCsv($file) {
    // $file = __DIR__ . '/对账明细文件.txt';
    $fh = fopen($file, "r");
    if ($fh === false) {
        throw new \Exception('没有读取权限'.$file);
    }
    $row = 0;
    $labelList = [];
    $data = [];
    while (($item = fgetcsv($fh, 0, '|', )) !== FALSE) {
        $valueLength = count($item);
        if ($valueLength === 0 || ($valueLength === 1 && $item[0] === '') )  {
            continue; // 无效数据
        }

        if ($row === 0) { // 标题行
            $labelList = $item;
        }else{ // 数据行
            $tmp = [];
            for ($valueIndex = 0; $valueIndex < $valueLength; $valueIndex++) {
                $label = $labelList[$valueIndex];
                $value = $item[$valueIndex];
                $value = iconv('GBK', 'UTF-8//TRANSLIT//IGNORE', $value);
                $tmp[$label] = $value;
            }
            $data[] = $tmp;
        }
        $row++;
    }
    fclose($fh);
    return $data;
}

$fullFileName = mb_convert_encoding( $fullFileName, 'UTF-8', 'UTF-8,GBK,GB2312,BIG5' );
$fullFileName = iconv('GBK', 'UTF-8//TRANSLIT//IGNORE', $fullFileName);

优先使用第一种, 第二种失败的话会报错notice级别错误 iconv(): Unknown error (22)