在每个工作站上打开 mysql 连接
由于我对问题的解释似乎不够清楚，让我再试一次。
设置：
我们的 Web 应用程序使用单个 mySQL 用户连接到数据库，这或多或少是经典的方法。为了避免混淆，我将 mysql 用户称为 sqluser，并将登录 Web 应用程序的用户简称为 user。现在，用户从其工作站（可以是台式机或笔记本电脑（主要是笔记本电脑））登录 Web 应用程序。
场景：
我将使用我们的网络应用程序中的报告模块来说明问题。当用户向系统请求报告时，我们使用临时表来提取、处理和格式化数据——php接收准备好的结果集来显示，php端不进行任何数据处理。问题是临时表或多或少在 mysql 连接之间随机共享，这使我们相信其中一些连接被重用。这是不可接受的，因为生成相同报告（但来自不同时间范围）的两个用户会覆盖彼此的临时表。
目前，我们通过事务克服了这个问题：每个报告请求都包装在一个事务中 - 在其中创建和删除临时表，使它们对并行或重用连接透明。
我们想要什么：
我们希望每个不共享临时表的用户/工作站都有一个新的、非共享的连接。换句话说，我们希望每个连接到数据库的用户都有一个新的 mysql 连接，该连接将保留其临时表，但不会在与不同用户的并行连接之间共享它们。
潜在的解决方案：
是为每个Web应用程序用户创建一个匹配的sqluser，并使用此sqluser为给定用户进行数据库连接。由于此解决方案提供了额外的复杂性（管理 sqlusers 与用户的匹配），我宁愿这不是首选。
问题：
我们能否以某种方式强制 mysql 在每个用户/工作站的基础上打开 mysql 连接，而不会跨连接池共享临时表？是否有一个框架（php 或 python）具有开箱即用的功能？
我希望这个解释比原来的解释稍微清楚一些。
原文：
是否可以打开mysql 每个工作站的连接 当登录同一个mysql时 用户？
这是场景：我们的 Web 应用程序 有正在使用连接到mysql 单个 mysql 用户，什么使 mysql 重用开放的连接，有什么好处 到某一点。当我们 需要创建临时表 每台机器的基础上。目前 解决方法是使用事务和 之后删除临时表， 但我们希望它们可以重复使用 在工作站的会话内（在 webapp 用户上下文）。
另一个解决方案是创建 每个 webapp 用户的 mysql 用户和 打开该用户的连接 登录。但接下来你必须管理 不断的轮换和匹配 mysql 用户到 webapp 用户。
相反，我们想以某种方式强制 mysql 打开新连接 webapp 用户，可以使用 工作站的内部IP、mac地址、 等等。任何人都知道这是否是偶数 可能吗？
我们计划重写这个 webapp从头开始，所以基本上均匀 真正深刻的改变是可能的 至此，问题就来了。 也许有 php （或 python） 可以实现这个目标的框架 开箱即用？
Since my explanation of the problem seems not to be clear enough, let me give it another shot.
Setup:
The web application we have uses single mySQL user to connect to the DB, what is more or less classic approach. To avoid confusion I'm going to refer to mysql user(s) as sqluser and to users that log into web applications as simply users. Now, users log in to the web application from their workstations which can be desktops or laptops (mainly laptops).
Scenario:
I'll use reporting module from our web application to illustrate the problem. When the user request a report from the system, we use temporary tables to pull, process and format data – php receives ready resultset to display, no data processing is done on php side. The problem is that temporary tables are more or less randomly shared across mysql connections, what leads us to believe some of those connections are reused. It is unacceptable, because two users generating same report (but say from different time range) would overwrite each other's temporary tables.
Currently we overcame this problem with transactions: each report request is wrapped inside a transaction - temporary tables are created and dropped within, making them transparent to parallel or reused connections.
What we want:
We want a new, unshared connection per each user/workstation that does not share temporary tables. In other words we want a new mysql connection for each user connecting to DB that will retain its temporary tables but not share them across parallel connections with different users.
Potential solution:
Is to make a matching sqluser for each web application user and use this sqluser for DB connection for given user. Due to additional layer of complexity (managing the matchup of sqlusers to users) this solution provides, I'd rather this wasn't out first choice.
Question:
Can we somehow force mysql to open mysql connection on per user/workstation basis that would not share temporary tables across connection pool? is there a framework (php or python) out there that would have such functionality out-of-the-box?
I hope this explanation is slightly clearer then the original.
Original text:
Is it possible to open mysql
connection on per workstation basis
when logging on to the same mysql
user?Here's the scenario: the webapp we
have is connecting to mysql using
single mysql user, what makes mysql
reuse open connections, what is good
to a point. The problem starts when we
need to create temporary tables on
per-machine basis. Currently the
workaround is to use transactions and
remove temporary tables afterwards,
but we'd like them to be reusable
within workstation's session (within
webapp user context).Another solution would be to create
mysql user for each webapp user and
open connection on that user upon
login. But then you have to manage
constant rotation and match-up of
mysql users to webapp users.Instead we'd like to somehow force
mysql to open new connection per
webapp user, either using
workstation's internal IP, macaddress,
etc. Anybody knows if this is even
possible?We are planing on rewriting this
webapp from scratch, so basically even
really deep changes are possible at
this point, hence the question.
Perhaps there's php (or python)
framework that can achieve this
out-of-the-box?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
MySQL 文档非常清楚地解决了您的问题：
来源： http://dev.mysql.com/doc/refman /5.5/en/create-table.html
是什么导致您假设临时表在多个连接之间共享？
当您连接到数据库时，
除非
MySQL documentation addresses your problem quite clearly:
Source: http://dev.mysql.com/doc/refman/5.5/en/create-table.html
What led to your assumption, that tmp tables are shared between several connections?
When you connect to the database
UNLESS
如果我理解正确，您希望该设施拥有（实际上）一个临时表，其中范围不仅仅是当前会话，而是该用户和客户端地址的所有会话。如果是这样，打开更多连接与该问题无关。您需要重写 mysqld 来适应这一点！
否 - 仅当您使用连接池/持久连接时。
，这意味着临时表的结构数量有限 - 如果是这种情况，为什么不直接创建这些带有附加列（由触发器填充？）的永久表来保留用户名？然后，如果您确实想要学究气，请阻止对表的 SELECT 访问并强制用户从过滤其用户名的视图中检索数据。
呃...不-除了不可能的事实之外，实现管理数据库内部数据结构的逻辑形式也是错误的地方。
如果这是一个潜在的解决方案，那么您还没有很好地解释问题。
听起来您正在尝试模拟事务跨越多个页面请求（因此也跨越多个 mysql 会话）的行为。在这种情况下，数据库临时表与任何可行的解决方案无关。
If I've understood you right you want the facility to have (in effect) a temporary table where the scope is not just the current session, but all sessions for that user and client address. If so, opening more connections is nothing to do with the problem. You'd need to rewrite the mysqld to accomodate this!
No - only if you use connection pooling / persistent connections.
That sort of implies that there is a finite number of structures for the temporary tables - if this is the case, why not just create these are permanent tables with an additional column (populated by a trigger?) to hold the username? Then, if you really want to be pedantic, block SELECT access to the table and force users to retrieve data from a view which filters their username.
erm...no - in addition to the fact it would not be possible, it would be the wrong place to imlement logic form managing the databases internal data structures.
If that's a potential solution then you've not explained the problem very well.
It sounds like you're trying to emulate behaviour where a transaction spans multiple page requests (and therefore multiple mysql sessions). In which case database temporary tables have nothing to do with any viable solution.