是否可以在不停机的情况下将 2 个 MySQL rds 合并为一个？他们都没有任何重叠的数据库
我正在考虑通过 mysql 转储将数据库从一个数据库加载到另一个数据库，然后从其他 rds 复制以赶上进度，然后进行故障转移
这可能吗？
我使用 amazon rds 并有一个跨区域副本。当我进行一些架构更改时，我的只读副本给了我这个错误并且复制停止了。
消息：读取副本复制错误 - SQLError：1146，原因：错误表 phppoint_koreannoona.phppos_items 在查询中不存在。默认数据库：DB_NAME；。查询：ALTER TABLE phppos_items DROP COLUMN aaatex_qb_item_name
什么会导致这个？桌子在主人身上。我的迁移确实删除了该字段，但从属中缺少整个表。
我不得不重建奴隶。
我的 RDS MySQL 5.6.34 服务器在正常负载下崩溃，今天没有任何异常（我能找到）。亚马逊支持向我指出了错误日志。
MySQL 配置： https ://gist.github.com/blasto333/9e82b6261681303f7369c3a2652e03f2
您可以在以下位置查看完整的错误日志：
https://www.dropbox.com/s/8y10mfok4l6zc1m/mysql-error-running.log.21.txt?dl=0
大多数错误日志都有：
InnoDB：警告：信号量等待很长：--线程 47504778909440 在 dict0dict.cc 第 1122 行等待了 241.00 秒信号量：0x2b33b16f34a8 处的互斥量创建文件 dict0dict.cc 第 1057 行，锁定 var 1 服务员标志 1 InnoDB：警告：很长信号量等待：
也有死锁
LATEST DETECTED DEADLOCK
------------------------
2018-07-05 23:11:31 2b34c9f8d700
*** (1) TRANSACTION:
TRANSACTION 1056966456, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 22 lock struct(s), heap size 2936, 10 row lock(s), undo log entries 8
MySQL thread id 149979681, OS thread handle 0x2b348aea9700, query id 1268732633 172.31.43.29 phppoint updating
UPDATE `phppos_items` SET `last_modified` = '2018-07-05 18:11:31'
WHERE `item_id` = 9794
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1273297 n bits 152 index `PRIMARY` of table `phppoint_fatpanda`.`phppos_items` trx id 1056966456 lock_mode X locks rec but not gap waiting
Record lock, heap no 30 PHYSICAL RECORD: n_fields 61; compact format; info bits 0
0: len 4; hex 80002642; asc &B;;
1: len 6; hex 00003f00027d; asc ? };;
2: len 7; hex 1b00013aea2ab2; asc : * ;;
3: len 24; hex 4650205468757273646179204368756262792050726f6d6f; asc FP Thursday Chubby Promo;;
4: len 4; hex 80000041; asc A;;
知道我可以从哪里开始寻找吗？
mysql> show global status;
+-----------------------------------------------+--------------+
| Variable_name | Value |
+-----------------------------------------------+--------------+
| Aborted_clients | 75 |
| Aborted_connects | 767 |
| Binlog_cache_disk_use | 23592 |
| Binlog_cache_use | 1910314 |
| Binlog_stmt_cache_disk_use | 0 |
| Binlog_stmt_cache_use | 9909 |
| Bytes_received | 19020005934 |
| Bytes_sent | 450148820308 |
| Com_admin_commands | 9 |
| Com_assign_to_keycache | 0 |
| Com_alter_db | 0 |
| Com_alter_db_upgrade | 0 |
| Com_alter_event | 0 |
| Com_alter_function | 0 |
| Com_alter_procedure | 0 |
| Com_alter_server | 0 |
| Com_alter_table | 6097 |
| Com_alter_tablespace | 0 |
| Com_alter_user | 0 |
| Com_analyze | 0 |
| Com_begin | 50002 |
| Com_binlog | 0 |
| Com_call_procedure | 66 |
| Com_change_db | 332896 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 50169 |
| Com_create_db | 25 |
| Com_create_event | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_procedure | 0 |
| Com_create_server | 0 |
| Com_create_table | 2822 |
| Com_create_trigger | 0 |
| Com_create_udf | 0 |
| Com_create_user | 0 |
| Com_create_view | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 70492 |
| Com_delete_multi | 148 |
| Com_do | 0 |
| Com_drop_db | 1 |
| Com_drop_event | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_procedure | 0 |
| Com_drop_server | 0 |
| Com_drop_table | 615 |
| Com_drop_trigger | 0 |
| Com_drop_user | 0 |
| Com_drop_view | 0 |
| Com_empty_query | 0 |
| Com_execute_sql | 0 |
| Com_flush | 2023 |
| Com_get_diagnostics | 0 |
| Com_grant | 2 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 494163 |
| Com_insert_select | 0 |
| Com_install_plugin | 0 |
| Com_kill | 66 |
| Com_load | 0 |
| Com_lock_tables | 2819 |
| Com_optimize | 97 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 662 |
| Com_purge_before_date | 0 |
| Com_release_savepoint | 0 |
| Com_rename_table | 0 |
| Com_rename_user | 0 |
| Com_repair | 0 |
| Com_replace | 47982 |
| Com_replace_select | 9 |
| Com_reset | 0 |
| Com_resignal | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 65 |
| Com_rollback_to_savepoint | 0 |
| Com_savepoint | 0 |
| Com_select | 14400535 |
| Com_set_option | 11497318 |
| Com_signal | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 33 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_create_db | 1717 |
| Com_show_create_event | 0 |
| Com_show_create_func | 0 |
| Com_show_create_proc | 208 |
| Com_show_create_table | 486369 |
| Com_show_create_trigger | 40 |
| Com_show_databases | 53 |
| Com_show_engine_logs | 0 |
| Com_show_engine_mutex | 0 |
| Com_show_engine_status | 2 |
| Com_show_events | 20 |
| Com_show_errors | 0 |
| Com_show_fields | 2087822 |
| Com_show_function_code | 0 |
| Com_show_function_status | 3436 |
| Com_show_grants | 10 |
| Com_show_keys | 42 |
| Com_show_master_status | 13 |
| Com_show_open_tables | 0 |
| Com_show_plugins | 0 |
| Com_show_privileges | 0 |
| Com_show_procedure_code | 0 |
| Com_show_procedure_status | 3436 |
| Com_show_processlist | 119951 |
| Com_show_profile | 0 |
| Com_show_profiles | 0 |
| Com_show_relaylog_events | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 10 |
| Com_show_status | 1917 |
| Com_show_storage_engines | 0 |
| Com_show_table_status | 324246 |
| Com_show_tables | 1276858 |
| Com_show_triggers | 324172 |
| Com_show_variables | 2051 |
| Com_show_warnings | 4 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 3 |
| Com_stmt_execute | 23 |
| Com_stmt_fetch | 10 |
| Com_stmt_prepare | 23 |
| Com_stmt_reprepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 252 |
| Com_uninstall_plugin | 0 |
| Com_unlock_tables | 4537 |
| Com_update | 1876581 |
| Com_update_multi | 0 |
| Com_xa_commit | 0 |
| Com_xa_end | 0 |
| Com_xa_prepare | 0 |
| Com_xa_recover | 0 |
| Com_xa_rollback | 0 |
| Com_xa_start | 0 |
| Compression | OFF |
| Connection_errors_accept | 0 |
| Connection_errors_internal | 0 |
| Connection_errors_max_connections | 0 |
| Connection_errors_peer_address | 0 |
| Connection_errors_select | 0 |
| Connection_errors_tcpwrap | 0 |
| Connections | 14109046 |
| Created_tmp_disk_tables | 3483968 |
| Created_tmp_files | 27074 |
| Created_tmp_tables | 5675076 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 16730928 |
| Handler_delete | 58746 |
| Handler_discover | 0 |
| Handler_external_lock | 44461222 |
| Handler_mrr_init | 0 |
| Handler_prepare | 4935298 |
| Handler_read_first | 555947737 |
| Handler_read_key | 4342441284 |
| Handler_read_last | 51021 |
| Handler_read_next | 4410037167 |
| Handler_read_prev | 1041500 |
| Handler_read_rnd | 66317337 |
| Handler_read_rnd_next | 4061126421 |
| Handler_rollback | 14002 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 6298422 |
| Handler_write | 528228581 |
| Innodb_buffer_pool_dump_status | not started |
| Innodb_buffer_pool_load_status | not started |
| Innodb_buffer_pool_pages_data | 157888 |
| Innodb_buffer_pool_bytes_data | 2586836992 |
| Innodb_buffer_pool_pages_dirty | 1456 |
| Innodb_buffer_pool_bytes_dirty | 23855104 |
| Innodb_buffer_pool_pages_flushed | 6097239 |
| Innodb_buffer_pool_pages_free | 8192 |
| Innodb_buffer_pool_pages_misc | 0 |
| Innodb_buffer_pool_pages_total | 166080 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 4175098 |
| Innodb_buffer_pool_read_ahead_evicted | 1998 |
| Innodb_buffer_pool_read_requests | 20669375353 |
| Innodb_buffer_pool_reads | 19725302 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 36813736 |
| Innodb_data_fsyncs | 4000141 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 394739994624 |
| Innodb_data_reads | 24092362 |
| Innodb_data_writes | 9335083 |
| Innodb_data_written | 210941677568 |
| Innodb_dblwr_pages_written | 6097239 |
| Innodb_dblwr_writes | 577145 |
| Innodb_have_atomic_builtins | ON |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 20665741 |
| Innodb_log_writes | 2583719 |
| Innodb_os_log_fsyncs | 2659640 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 11108775936 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 323226 |
| Innodb_pages_read | 24091712 |
| Innodb_pages_written | 6097239 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 658 |
| Innodb_row_lock_time_avg | 5 |
| Innodb_row_lock_time_max | 19 |
| Innodb_row_lock_waits | 123 |
| Innodb_rows_deleted | 58747 |
| Innodb_rows_inserted | 503434 |
| Innodb_rows_read | 9533246024 |
| Innodb_rows_updated | 2062321 |
| Innodb_num_open_files | 14 |
| Innodb_truncated_status_writes | 0 |
| Innodb_available_undo_logs | 128 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 13396 |
| Key_blocks_used | 3898 |
| Key_read_requests | 968442355 |
| Key_reads | 260 |
| Key_write_requests | 314009997 |
| Key_writes | 0 |
| Last_query_cost | 0.000000 |
| Last_query_partial_plans | 0 |
| Max_used_connections | 429 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 3 |
| Open_streams | 0 |
| Open_table_definitions | 1400 |
| Open_tables | 2000 |
| Opened_files | 15050970 |
| Opened_table_definitions | 1057163 |
| Opened_tables | 4565317 |
| Performance_schema_accounts_lost | 0 |
| Performance_schema_cond_classes_lost | 0 |
| Performance_schema_cond_instances_lost | 0 |
| Performance_schema_digest_lost | 0 |
| Performance_schema_file_classes_lost | 0 |
| Performance_schema_file_handles_lost | 0 |
| Performance_schema_file_instances_lost | 0 |
| Performance_schema_hosts_lost | 0 |
| Performance_schema_locker_lost | 0 |
| Performance_schema_mutex_classes_lost | 0 |
| Performance_schema_mutex_instances_lost | 0 |
| Performance_schema_rwlock_classes_lost | 0 |
| Performance_schema_rwlock_instances_lost | 0 |
| Performance_schema_session_connect_attrs_lost | 0 |
| Performance_schema_socket_classes_lost | 0 |
| Performance_schema_socket_instances_lost | 0 |
| Performance_schema_stage_classes_lost | 0 |
| Performance_schema_statement_classes_lost | 0 |
| Performance_schema_table_handles_lost | 0 |
| Performance_schema_table_instances_lost | 0 |
| Performance_schema_thread_classes_lost | 0 |
| Performance_schema_thread_instances_lost | 0 |
| Performance_schema_users_lost | 0 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 4359 |
| Qcache_free_memory | 11838224 |
| Qcache_hits | 53345695 |
| Qcache_inserts | 10498816 |
| Qcache_lowmem_prunes | 4945792 |
| Qcache_not_cached | 3901891 |
| Qcache_queries_in_cache | 11254 |
| Qcache_total_blocks | 32300 |
| Queries | 100927051 |
| Questions | 100924711 |
| Select_full_join | 1119531 |
| Select_full_range_join | 1691 |
| Select_range | 976854 |
| Select_range_check | 37 |
| Select_scan | 5601093 |
| Slave_heartbeat_period | 0.000 |
| Slave_last_heartbeat | |
| Slave_open_temp_tables | 0 |
| Slave_received_heartbeats | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 1045 |
| Sort_merge_passes | 7119 |
| Sort_range | 246182 |
| Sort_rows | 66189786 |
| Sort_scan | 1565455 |
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | |
| Ssl_cipher_list | |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_default_timeout | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_server_not_after | |
| Ssl_server_not_before | |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | Unknown |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | |
| Table_locks_immediate | 22141644 |
| Table_locks_waited | 0 |
| Table_open_cache_hits | 20638283 |
| Table_open_cache_misses | 4565315 |
| Table_open_cache_overflows | 4559458 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 53 |
| Threads_connected | 19 |
| Threads_created | 31446 |
| Threads_running | 2 |
| Uptime | 601636 |
| Uptime_since_flush_status | 601636 |
+-----------------------------------------------+--------------+
341 rows in set (0.00 sec)
我知道迁移到 Aurora 只是从 MySQL 源创建只读副本，然后在赶上时升级并停止在 MySQL RDS 以前的主服务器上写入。
我使用具有 CNAME 记录的 Route 53 DNS。我的计划是将 DNS 更改为 Aurora 集群，但不确定这是否即时？有没有办法停止旧的 MySQL RDS 实例，使其无法写入？（它作为一个多可用区，没有停止）
我有下表：（建表语句）
CREATE TABLE `phppos_people` (
`first_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`last_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`phone_number` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`address_1` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`address_2` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`city` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`state` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`zip` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`country` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`comments` text COLLATE utf8_unicode_ci NOT NULL,
`image_id` int(10) DEFAULT NULL,
`person_id` int(10) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`person_id`),
KEY `phppos_people_ibfk_1` (`image_id`),
FULLTEXT KEY `full_search` (`first_name`,`last_name`,`email`,`phone_number`),
FULLTEXT KEY `first_name_search` (`first_name`),
FULLTEXT KEY `last_name_search` (`last_name`),
FULLTEXT KEY `full_name_search` (`first_name`,`last_name`),
FULLTEXT KEY `email_search` (`email`),
FULLTEXT KEY `phone_number_search` (`phone_number`),
CONSTRAINT `phppos_people_ibfk_1` FOREIGN KEY (`image_id`) REFERENCES `phppos_app_files` (`file_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
我需要一次使用名字、姓氏、电子邮件和电话号码进行快速搜索；而且还能够搜索每个单独的字段。我也喜欢 Btree 无法完成的全文相关性排序
查询运行良好，但根据我的云数据库提供商的说法，他们推荐使用 btree 索引以获得更好的内存和磁盘使用率。
我是否有任何其他选项/配置或方法来创建不使用大量磁盘+内存的索引？每个数据库有大约 40 个遵循类似模式的全文索引。这些索引的性能非常好，并且确实加快了产品速度。
Innodb全文设计是为了占用大量内存+磁盘吗？这是我的mysql变量设置
https://gist.github.com/blasto333/aa4241a4e37447961188356719ea6984
这是我的提供商的一些统计数据
经过审查，我们了解了存储在 InnoDB 中的实际数据集大小。当我发现您的数据集大小比您的磁盘使用量小得多时，我有点惊讶，如下所示：
磁盘和表空间使用率之间的这种差异最初让我担心我们有一些孤立的东西，比如旧的临时表，但后来发现这是由InnoDB 全文索引的大量使用引起的。
PHP-POS-DB-01
On Disk: 18GB
Total DataSet Size: 4.56GB
Unused allocated Tablespace: 3.10GB ## Fragmentation
Total innodb used plus fragmentation: 7.66GB
PHP-POS-DB-2-01
On Disk: 9.7G
Total DataSet Size: 923.85MB
Unused allocated Tablespace: 363.00MB
Total innodb used plus fragmentation: 1.29GB
我正在将全文搜索添加到已经存在的具有 id 整数的主自动递增键的 mysql 表中。
当我添加全文索引时，我收到警告：
/*!50604 ALTER TABLE `phppos_additional_item_numbers` ADD FULLTEXT INDEX item_number_search (`item_number`)*/;
Warning (Code 124): InnoDB rebuilding table to add column FTS_DOC_ID
我不能简单地重构我的所有代码以添加列 FTS_DOC_ID 来删除此警告。允许这个警告发生是不是很糟糕？
在 mysql 5.7 中更改会话模式时，我收到以下警告。
"(Code 3090): Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release."
我不完全明白它的意思。mysql 是否删除了能够设置会话模式的能力？
mysql终端
mysql> \W
Show warnings enabled.
mysql> SET SESSION sql_mode="NO_ENGINE_SUBSTITUTION";
Query OK, 0 rows affected, 1 warning (0.00 sec)
Warning (Code 3090): Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release.
mysql>
我希望为 MySQL 中的密码重置生成一个加密安全字符串。我知道我可以在 PHP 的应用程序级别做到这一点；但这需要我的许多客户可能没有的 OpenSSL 扩展。
如果我可以在 MySQL 5 中以一种广泛可用的安全方式做到这一点，那将是理想的。这可能吗？
注意：这是为了生成密码重置的安全令牌。它与安全连接没有任何关系，因此使用 https 不是解决方案。
我正在对包含 130k+ 记录的表进行复杂的全文搜索。完成需要 0.5 秒。我希望它会快一点。
有什么我可以做的让它更快吗？
下面是查询连同解释+相关表
Query:
SELECT `phppos_items`.*, `phppos_categories`.`name` as category, `phppos_location_items`.`quantity` as quantity, `phppos_location_items`.`reorder_level` as location_reorder_level, `phppos_location_items`.`cost_price` as location_cost_price, `phppos_location_items`.`unit_price` as location_unit_price
FROM (`phppos_items`)
LEFT JOIN `phppos_categories` ON `phppos_categories`.`id` = `phppos_items`.`category_id`
LEFT JOIN `phppos_location_items` ON `phppos_location_items`.`item_id` = `phppos_items`.`item_id` and location_id = 1
LEFT JOIN `phppos_additional_item_numbers` ON `phppos_additional_item_numbers`.`item_id` = `phppos_items`.`item_id`
LEFT JOIN `phppos_items_tags` ON `phppos_items_tags`.`item_id` = `phppos_items`.`item_id`
LEFT JOIN `phppos_tags` ON `phppos_tags`.`id` = `phppos_items_tags`.`tag_id`
WHERE (MATCH (phppos_items.name, phppos_items.item_number, product_id, description) AGAINST ('test')
or MATCH(phppos_tags.name) AGAINST ('test') or
MATCH(phppos_categories.name) AGAINST ('test') or
MATCH(phppos_additional_item_numbers.item_number) AGAINST ('test'))and phppos_items.deleted=0
LIMIT 20
EXPLAIN:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------------------+--------+--------------------------------------+---------+---------+--------------------------------+-------+-------------+
| 1 | SIMPLE | phppos_items | ref | deleted | deleted | 4 | const | 66679 | NULL |
| 1 | SIMPLE | phppos_categories | eq_ref | PRIMARY | PRIMARY | 4 | pos.phppos_items.category_id | 1 | NULL |
| 1 | SIMPLE | phppos_location_items | eq_ref | PRIMARY,phppos_location_items_ibfk_2 | PRIMARY | 8 | const,pos.phppos_items.item_id | 1 | NULL |
| 1 | SIMPLE | phppos_additional_item_numbers | ref | PRIMARY | PRIMARY | 4 | pos.phppos_items.item_id | 1 | Using index |
| 1 | SIMPLE | phppos_items_tags | ref | PRIMARY | PRIMARY | 4 | pos.phppos_items.item_id | 1 | Using index |
| 1 | SIMPLE | phppos_tags | eq_ref | PRIMARY | PRIMARY | 4 | pos.phppos_items_tags.tag_id | 1 | Using where |
+----+-------------+--------------------------------+--------+--------------------------------------+---------+---------+--------------------------------+-------+-------------+
6 rows in set (0.00 sec)
mysql> show create table phppos_items;
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_items | CREATE TABLE `phppos_items` (
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`category_id` int(11) DEFAULT NULL,
`supplier_id` int(11) DEFAULT NULL,
`item_number` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`product_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`description` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`size` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`tax_included` int(1) NOT NULL DEFAULT '0',
`cost_price` decimal(23,10) NOT NULL,
`unit_price` decimal(23,10) NOT NULL,
`promo_price` decimal(23,10) DEFAULT NULL,
`start_date` date DEFAULT NULL,
`end_date` date DEFAULT NULL,
`reorder_level` decimal(23,10) DEFAULT NULL,
`expire` date DEFAULT NULL,
`item_id` int(10) NOT NULL AUTO_INCREMENT,
`allow_alt_description` tinyint(1) NOT NULL,
`is_serialized` tinyint(1) NOT NULL,
`image_id` int(10) DEFAULT NULL,
`override_default_tax` int(1) NOT NULL DEFAULT '0',
`is_service` int(1) NOT NULL DEFAULT '0',
`commission_percent` decimal(23,10) DEFAULT '0.0000000000',
`commission_fixed` decimal(23,10) DEFAULT '0.0000000000',
`deleted` int(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`item_id`),
UNIQUE KEY `item_number` (`item_number`),
UNIQUE KEY `product_id` (`product_id`),
KEY `phppos_items_ibfk_1` (`supplier_id`),
KEY `name` (`name`),
KEY `deleted` (`deleted`),
KEY `phppos_items_ibfk_2` (`image_id`),
KEY `phppos_items_ibfk_3` (`category_id`),
FULLTEXT KEY `full_search` (`name`,`item_number`,`product_id`,`description`),
FULLTEXT KEY `name_search` (`name`),
FULLTEXT KEY `item_number_search` (`item_number`),
FULLTEXT KEY `product_id_search` (`product_id`),
FULLTEXT KEY `description_search` (`description`),
FULLTEXT KEY `size_search` (`size`),
CONSTRAINT `phppos_items_ibfk_1` FOREIGN KEY (`supplier_id`) REFERENCES `phppos_suppliers` (`person_id`),
CONSTRAINT `phppos_items_ibfk_2` FOREIGN KEY (`image_id`) REFERENCES `phppos_app_files` (`file_id`),
CONSTRAINT `phppos_items_ibfk_3` FOREIGN KEY (`category_id`) REFERENCES `phppos_categories` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=133095 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table phppos_categories;
+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_categories | CREATE TABLE `phppos_categories` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`deleted` int(1) NOT NULL DEFAULT '0',
`parent_id` int(11) DEFAULT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
KEY `deleted` (`deleted`),
KEY `parent_id_name_index` (`parent_id`,`name`),
FULLTEXT KEY `name_search` (`name`),
CONSTRAINT `phppos_categories_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `phppos_categories` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4096 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table phppos_location_items;
+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_location_items | CREATE TABLE `phppos_location_items` (
`location_id` int(11) NOT NULL,
`item_id` int(11) NOT NULL,
`location` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`cost_price` decimal(23,10) DEFAULT NULL,
`unit_price` decimal(23,10) DEFAULT NULL,
`promo_price` decimal(23,10) DEFAULT NULL,
`start_date` date DEFAULT NULL,
`end_date` date DEFAULT NULL,
`quantity` decimal(23,10) DEFAULT '0.0000000000',
`reorder_level` decimal(23,10) DEFAULT NULL,
`override_default_tax` int(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`location_id`,`item_id`),
KEY `phppos_location_items_ibfk_2` (`item_id`),
CONSTRAINT `phppos_location_items_ibfk_1` FOREIGN KEY (`location_id`) REFERENCES `phppos_locations` (`location_id`),
CONSTRAINT `phppos_location_items_ibfk_2` FOREIGN KEY (`item_id`) REFERENCES `phppos_items` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table phppos_additional_item_numbers;
+--------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_additional_item_numbers | CREATE TABLE `phppos_additional_item_numbers` (
`item_id` int(11) NOT NULL AUTO_INCREMENT,
`item_number` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`item_id`,`item_number`),
UNIQUE KEY `item_number` (`item_number`),
FULLTEXT KEY `item_number_search` (`item_number`),
CONSTRAINT `phppos_additional_item_numbers_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `phppos_items` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+--------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table phppos_items_tags;
+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_items_tags | CREATE TABLE `phppos_items_tags` (
`item_id` int(11) NOT NULL,
`tag_id` int(11) NOT NULL,
PRIMARY KEY (`item_id`,`tag_id`),
KEY `phppos_items_tags_ibfk_2` (`tag_id`),
CONSTRAINT `phppos_items_tags_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `phppos_items` (`item_id`),
CONSTRAINT `phppos_items_tags_ibfk_2` FOREIGN KEY (`tag_id`) REFERENCES `phppos_tags` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table phppos_tags;
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_tags | CREATE TABLE `phppos_tags` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`deleted` int(1) NOT NULL DEFAULT '0',
`name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `tag_name` (`name`),
KEY `deleted` (`deleted`),
FULLTEXT KEY `name_search` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
我找到了关于如何为执行 mysql 语句提供条件逻辑的答案 ( https://stackoverflow.com/a/7537348/627473 )。似乎工作得很好。
但我需要找到一种方法来针对非常具体的条件执行代码。仅当数据库支持 innodb 的 FULL TEXT 索引时，代码才能执行。这发生在 mysql 5.6.4 或更高版本中。但是我也需要这个来为 maria db 的其他 mysql 分支工作。
我确定我可以找到 mysql 版本的方法；但我真的不喜欢依赖版本，而宁愿依赖功能检测。
有没有办法做到这一点？
编辑：这是代码。我只是有更多错误，我不确定是否可以修复。
在 phpMyAdmin"#2014 - Commands out of sync; you can't run this command now"中显示错误。CLI 不会发生这种情况。有没有解决的办法？
delimiter //
create procedure update_phppos()
begin
set @supports_ft := (SELECT COUNT(*) FROM information_schema.GLOBAL_VARIABLES WHERE Variable_name = 'innodb_ft_cache_size');
if @supports_ft > 0 then
REPLACE INTO `phppos_app_config` (`key`, `value`) VALUES ('version', '15.0');
else
SELECT 'An error has occured. You MUST have mysql 5.6.4 or higher to update php point of sale' as 'ERROR';
end if;
end;
//
delimiter ;
-- Execute the procedure
call update_phppos();
-- Drop the procedure
drop procedure update_phppos;
当我尝试将不同的值插入表中时，为什么需要进行另一次选择？另外：有没有更好的方法来做到这一点？
作品：
INSERT INTO phppos_categories (name)
SELECT category FROM
(SELECT DISTINCT category FROM phppos_items as items
UNION
SELECT DISTINCT category FROM phppos_item_kits as item_kits)
as category_result;
不起作用：
INSERT INTO phppos_categories (name)
(SELECT DISTINCT category FROM phppos_items as items
UNION
SELECT DISTINCT category FROM phppos_item_kits as item_kits)
我有一个用于 mysql 临时目录的 768M tmpfs 目录。在大日期范围内运行报告时，它偶尔会填满。当发生这种情况时，报告失败并且我收到错误 28。
这实际上对我来说似乎可以接受，因为它可以防止查询运行时间过长并给服务器增加负载。（我认为）
使用 tmpfs 的优势是使用临时表的查询运行速度提高了 50%。
我的问题是：当插入或替换正在运行时 tmpfs 是否已满，这会失败吗？
此外，当 mysql 将目录填满时，当查询失败时，文件将被删除。会有没有的情况吗？
你认为我对 MySQL 临时目录使用 /tmp 会导致任何问题吗？我有大约 50 个并发连接，但并非所有连接都同时运行报告。
不使用 tmpfs 的优缺点是什么？
我有以下查询：
SELECT COUNT(*) AS `numrows`
FROM (`phppos_items`)
WHERE `deleted` = 0
解释：
mysql> explain SELECT COUNT(*) AS `numrows`
-> FROM (`phppos_items`)
-> WHERE `deleted` = 0;
+----+-------------+--------------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+------+---------------+---------+---------+-------+------+-------------+
| 1 | SIMPLE | phppos_items | ref | deleted | deleted | 4 | const | 1 | Using index |
+----+-------------+--------------+------+---------------+---------+---------+-------+------+-------------+
随着表中行的增长，此查询是否总是被索引并快速？
我向我的 mysql 服务器添加了更多表。增加 20% 的表都是 innodb。我有 200 多个使用相同模式的数据库。
添加更多表会增加内存使用量吗？我在使用 1.4-1.5gb 之前为 mysql 使用了大约 1.7gb 的 ram