create temporary table if not exists tmp engine=memory
SELECT id, CONCAT(TRIM(lastName),TRIM(firstName),TRIM(zip)) AS identify
FROM customers
GROUP BY identify;
运行过程时,我会收到以下错误消息:
The used table type doesn't support BLOB/TEXT columns
我已经看到了这个线程,但对我没有帮助。
列上的类型如下:
lastName -> VARCHAR(255)
firstName -> VARCHAR(255)
zip -> VARCHAR(10)
当我将邮政编码排除在该过程中的工作原理时,我想varchar的长度存在问题?
是否有人知道解决方案,而无需更改10到255的ZIP的VARCHAR长度?
发病率由常数 CONVERT_IF_BIGGER_TO_BLOB
的值显示出:
/**
CHAR and VARCHAR fields longer than this number of characters are converted
to BLOB.
Non-character fields longer than this number of bytes are converted to BLOB.
Comparisons should be '>' or '<='.
*/
#define CONVERT_IF_BIGGER_TO_BLOB 512 /* Used for CREATE ... SELECT */
mysql-server/sql/sql_const.h :: 52
和
16.3内存存储引擎
...
支持内存不支持的可变长度数据类型(包括斑点和文本(。
示例:
mysql> DROP TEMPORARY TABLE IF EXISTS `tmp`;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TEMPORARY TABLE IF NOT EXISTS `tmp` ENGINE=MEMORY
-> SELECT SPACE(512) `tmp_col`;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> DROP TEMPORARY TABLE IF EXISTS `tmp`;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TEMPORARY TABLE IF NOT EXISTS `tmp` ENGINE=MEMORY
-> SELECT SPACE(513) `tmp_col`;
ERROR 1163 (42000): The used table type doesn't support BLOB/TEXT columns
尝试:
mysql> DROP TABLE IF EXISTS `tmp`, `customers`;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE IF NOT EXISTS `customers` (
-> `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> `lastName` VARCHAR(255) NOT NULL,
-> `firstName` VARCHAR(255) NOT NULL,
-> `zip` VARCHAR(10) NOT NULL
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TEMPORARY TABLE IF NOT EXISTS `tmp` (
-> `id` BIGINT UNSIGNED NOT NULL PRIMARY KEY,
-> `identify` VARCHAR(520) NOT NULL
-> ) ENGINE=MEMORY;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO `tmp`
-> SELECT `id`, CONCAT(TRIM(`lastName`),
-> TRIM(`firstName`),
-> TRIM(`zip`)) `identify`
-> FROM `customers`
-> GROUP BY `id`, `identify`;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
我在发动机=内存的经验是,charset也很重要。带有charset latin1的varchar(65000(工作;charset UTF8没有引擎=内存默认charset = latin1