在程序中创建表时,表名可能是通过程序拼接的,所有可能超长,报错ERROR 1059 (42000): Identifier name,如下
mysql> create table aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbc (id int not null) charset=utf8;
ERROR 1059 (42000): ERROR 1059 (42000) 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbc' is too long
其实MySQL中存在表名的最大成都限制,为64,宏定义如下
#define NAME_CHAR_LEN 64 /* Field/table name length */
创建表时,具体的表名检测函数为
enum_ident_name_check check_table_name(const char *name, size_t length,
bool check_for_path_chars)
{
// name length in symbols
size_t name_length= 0;
const char *end= name+length;
if (!length || length > NAME_LEN)
return IDENT_NAME_WRONG;
bool last_char_is_space= FALSE;
while (name != end)
{
last_char_is_space= my_isspace(system_charset_info, *name);
if (use_mb(system_charset_info))
{
int len=my_ismbchar(system_charset_info, name, end);
if (len)
{
name += len;
name_length++;
continue;
}
}
if (check_for_path_chars &&
(*name == '/' || *name == '\\' || *name == '~' || *name == FN_EXTCHAR))
return IDENT_NAME_WRONG;
name++;
name_length++;
}
if (last_char_is_space)
return IDENT_NAME_WRONG;
else if (name_length > NAME_CHAR_LEN)
return IDENT_NAME_TOO_LONG;
return IDENT_NAME_OK;
}
函数调用
do_command(THD*)
dispatch_command(THD*, COM_DATA const*, enum_server_command)
mysql_parse(THD*, Parser_state*)
parse_sql(THD*, Parser_state*, Object_creation_ctx*)
MYSQLparse(THD*)
st_select_lex::add_table_to_list(THD*, Table_ident*, st_mysql_lex_string*, unsigned long, thr_lock_type, enum_mdl_type, List<Index_hint>*, List<String>*, st_mysql_lex_string*)
check_table_name(char const*, unsigned long, bool)
其实类似的还有数据库名长度限制,用户名长度限制等,这些都是写死在数据库中的。所以如果在程序中涉及到表名拼接的情况,在建表前,可以先进行检测,防止超长。