MySQL中的drugname字段,有沒有辦法從mySQL中的一個字段中分離數據？-CSDN博客

We have a drug column in our database and this contains the Drug Name (e.g. Xanax) and its size (0.5mg).

我們的數據庫中有一個葯物專欄，其中包含葯物名稱(例如Xanax)及其大小(0.5mg)。

E.g. Drug Name: Xanax 0.5mg

例如。葯物名稱：Xanax 0.5mg

However, there's a need for us to separate the two data without creating a new column for the size as doing so will have a huge effect on the database structure. We just need to populate a list with just the drug name without its size based from this single field / column.

但是，我們需要將兩個數據分開而不創建大小的新列，因為這樣做會對數據庫結構產生巨大影響。我們只需要根據葯物名稱填充列表，而不是基於此單個字段/列的大小。

Is there a way to extract just the name of the drug? Let's say by forcing the user to add a parenthesis around the drug size (e.g. Xanax (0.5mg))? Meaning just extract all the string that comes before the first "(" character?

有沒有辦法只提取葯物的名稱？讓我們說通過強迫用戶在葯物大小周圍添加一個括號(例如Xanax(0.5mg))？意思是只提取第一個“(”字符之前的所有字符串？

Or is there a better way?

或者，還有更好的方法？

3 个解决方案

Try this:

嘗試這個：

mytable:

mytable中：

id name

1 Xanax (0.5mg)

Query:

查詢：

select id, substring_index(name,'(',1) from tb_mx;

Will return:

將返回：

1, Xanax

So use it accordingly.

所以相應地使用它。

You can store drug´s data in JSON format

您可以以JSON格式存儲葯物數據

{"some_unique_id":{

"name": "Xanax",

"quantity": "0.5mg"

}}

And then use Functions That Search JSON Values for MySQL 5.7 to get what parameter you need.

然后使用搜索MySQL 5.7的JSON值的函數來獲取所需的參數。

I have some experience with DB used in Pharma industry and I can say it's not ok do doit like that.

我有一些在制葯行業中使用DB的經驗，我可以說這樣做不行。

Here is what i think u must do (normalize)

這是我認為你必須做的(正常化)

Table UM (like mg,ml,etc) Table Packing (like quantity per pice, pice nr , FK ID_UM) Table Drugs (name, fk id_packing)

表UM(如mg，ml等)表包裝(如每個pice的數量，pice nr，FK ID_UM)表葯物(名稱，fk id_packing)

Don't worry about space. Tables UM and Packing will have alot of reused ID, and a column int take less that than varchar.

不要擔心空間。表UM和Packing將有很多重用ID，而int int比varchar少。

Or can used JSON ideea, but then you will have some problemes in reporting part.

或者可以使用JSON ideea，但是在報告部分時會遇到一些問題。