英文:
Is there any way to insert string in MySQL?
问题
我有一个名为Template的表格。其中有一个名为Message的列。例如：
-------------------------------
id name desc cretaedBy Message
-------------------------------
1 A blah today <td><tr>one</tr></td></body></html>
2 B blah lastday
3 C blah onemonth <tr><td>last</td><td>next</td></tr></body></html>
在这里，我需要在Message列中的</body></html>之前插入<div>blah blah some content</div>。如何查找位置并使用更新查询在特定位置更新。
这个表格只是用来举例说明的，但它可能包含1000条记录。实际上，我期望的是，例如，如果使用Java或任何编程语言...如果字符串包含结束的body标签，那么截取字符串并在结束body标签之前添加数据。
请有人帮助我在MySQL中实现这个。
英文:
I have a table Template. In that I have one column like Message.For example
-------------------------------
id name desc cretaedBy Message
-------------------------------
1 A blah today <td><tr>one</tr></td></body></html>
2 B blah lastday
3 C blah onemonth <tr><td>last</td><td>next</td></tr></body></html>
Here, i need to insert <div>blah blah some content</div> before </body></html> in Message-column.
How to find the position and update in particular position using update query.
This table shows 3records for example only..but it may contain1000records. Actually what i am expecting, for example if java or any programming language...if string contains end body tag , substring the string and add data before end body tag.
Anyone please help me in mysql
答案1
得分: 0
你可以使用更新语句：
UPDATE Template SET Message = ''<tr><td>last</td><td>next</td></tr><div>blah blah some content</div></body></html>''
WHERE Message = ''<tr><td>last</td><td>next</td></tr></body></html>'';
英文:
You can use an update statement:
UPDATE Template SET Message = '<tr><td>last</td><td>next</td></tr><div>blah blah some content</div></body></html>'
WHERE Message = '<tr><td>last</td><td>next</td></tr></body></html>';
答案2
得分: 0
尝试这个：
创建模板DDL语句：
CREATE TABLE `mytable` (
`id` bigint(10) NOT NULL AUTO_INCREMENT,
`name` varchar(100) unsigned DEFAULT NULL COMMENT 'a name comment',
`Message` varchar(5000) DEFAULT NULL COMMENT 'an address comment',
PRIMARY KEY (`id`),
UNIQUE KEY `id_UNIQUE` (`id`)
)
创建模板插入数据语句：
Insert into mytable (name,message) VALUES('A','<td><tr>one</tr></td>?</body></html>')
...
Insert into mytable (name,message) VALUES('C','<td><tr>last</tr></td>?</body></html>')
使用Prepare。您的数据将插入到SQL查询模板的问号位置。
mysql> PREPARE stmt1 FROM 'Insert into mytable (name,message) VALUES('A','<td><tr>one</tr></td>?</body></html>')';
Insert into mytable (name,message) VALUES('C','<td><tr>last</tr></td>?</body></html>')';
mysql> SET @a = '<div>blah blah some content</div>';
mysql> EXECUTE stmt1 USING @a;
mysql> DEALLOCATE PREPARE stmt1;
根据需要创建并填充表格中的数据。
英文:
Try this:
Create template DDL statement:
CREATE TABLE `mytable` (
`id` bigint(10) NOT NULL AUTO_INCREMENT,
`name` varchar(100) unsigned DEFAULT NULL COMMENT 'a name comment',
`Message` varchar(5000) DEFAULT NULL COMMENT 'an address comment',
PRIMARY KEY (`id`),
UNIQUE KEY `id_UNIQUE` (`id`))
Create template INSERT data statement:
Insert into mytable (name,message) VALUES('A','<td><tr>one</tr></td>?</body></html>')
...
Insert into mytable (name,message) VALUES('C','<td><tr>last</tr></td>?</body></html>')
Use Prepare. Your data will be inserted into the SQL query template at the question mark place.
mysql> PREPARE stmt1 FROM 'Insert into mytable (name,message) VALUES('A','<td><tr>one</tr></td>?</body></html>');
Insert into mytable (name,message) VALUES('C','<td><tr>last</tr></td>?</body></html>');';
mysql> SET @a = '<div>blah blah some content</div>';
mysql> EXECUTE stmt1 USING @a;
mysql> DEALLOCATE PREPARE stmt1;
Create and fill in the table with the data whenever necessary.
答案3
得分: 0
你可以使用 UPDATE 与 REPLACE，将 </body></html> 替换为 <div>blah blah some content</div></body></html>。对于 Message 值中没有包含 </body></html> 的情况，不会有任何变化：
UPDATE Template
SET Message = REPLACE(Message, '</body></html>', '<div>blah blah some content</div></body></html>')
;
SELECT * FROM Template
输出：
id name desc createdBy Message
1 A blah today <td><tr>one</tr></td>div>blah blah some content</div></body></html>
2 B blah lastday (null)
3 C blah onemonth <tr><td>last</td><td>next</td></tr>div>blah blah some content</div></body></html>
如果你的表很大，可以使用 WHERE Message LIKE ''%</body></html>'' 限定条件来加快 UPDATE 的速度。
英文:
You can use an UPDATE with a REPLACE, where you replace </body></html> with <div>blah blah some content</div></body></html>. In Message values which don't have </body></html> in them, nothing will happen:
UPDATE Template
SET Message = REPLACE(Message, '</body></html>', 'div>blah blah some content</div></body></html>')
;
SELECT * FROM Template
Output:
id name desc createdBy Message
1 A blah today <td><tr>one</tr></td>div>blah blah some content</div></body></html>
2 B blah lastday (null)
3 C blah onemonth <tr><td>last</td><td>next</td></tr>div>blah blah some content</div></body></html>
If your table is very big, qualifying the UPDATE with WHERE Message LIKE '%</body></html>' will speed it up.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论