如何取出每行的前5个字符？

发布于 2024-10-22 15:30:30 字数 209 浏览 4 评论 0原文

我有一个文本文件，在文件中我

22222   hihihi
33333   hihihi
kjhkh   hihihi

想编写一个应用程序来与 mysql 数据库进行比较，这样如果数据库中的 ID 与文本文件中的第一列相同，则它将通过用文本文件中第二列中的信息替换名称来更新数据。

我该怎么做？

原文

I have a text file, inside the file I have

22222   hihihi
33333   hihihi
kjhkh   hihihi

I want to write an application to compare with mysql database so that if my ID in the database is the same as the first column in the text file, it will update the data by replacing the name with the information in the second column in the text file.

How can I do this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

記柔刀 2024-10-29 15:30:31

这似乎是一个分隔文本文件。您可以使用文本驱动程序非常轻松地将数据加载到记录集中。我不喜欢 PHP，但我相信 PHP 有文本驱动程序。如果您的应用程序在 Windows 上运行，则 Microsoft Jet 支持可用于将数据放入记录集的文本驱动程序。

回复收藏 0 原文

我不是你的备胎 2024-10-29 15:30:30

正如您上面提到的，字段之间用制表符分隔，您可以将文件直接导入MySQL（假设id是该表的主键）：（

LOAD DATA INFILE 'data.txt' REPLACE INTO TABLE mytable (id,name);

注意：这不仅会更新，还会添加条目）

As you mentioned above that the fields are separated by a tab, you can import the file directly into MySQL (assuming id is the primary key of that table):

LOAD DATA INFILE 'data.txt' REPLACE INTO TABLE mytable (id,name);

(Note: this will not only update but also add entries)

回复收藏 0 原文

猫性小仙女 2024-10-29 15:30:30

您可以尝试将数据加载到临时表中，运行更新，然后删除临时表 - 类似于：（

CREATE TABLE dftmp(id VARCHAR(255), name VARCHAR(255));
LOAD DXTX INFILE 'datafile.txt' INTO TABLE dftmp(id,name);
UPDATE real_table,dftmp SET real_table.name=dftmp.name WHERE real_table.id=dftmp.id;
DROP TXBLE dftmp;

用 DATA 替换 DXTX，用 TABLE 替换 TXBLE - 就是上面的方式，因为我的工作有一个过滤系统）

请参阅< a href="http://dev.mysql.com/doc/refman/4.1/en/load-data.html" rel="nofollow">http://dev.mysql.com/doc/refman/4.1/ en/load-data.html 了解有关加载数据的更多信息。

主要在 PHP 中处理这个问题可能会比较慢，但如果你需要走这条路，那么它会是这样的：

# make database connection

# open file and get it's contents
$lines = file('datafile.txt');

# for each line in the file
#     split the line into its parts
#     update the DB
foreach ($lines as $line) {
    $line_ar = explode("/\s+/",$line);
    mysql_query("UPDATE t SET name='$line_ar[1]' WHERE id='$line_ar[0]'");
}

# close the database connection

上述代码的潜在陷阱：

非常大的文件：你需要在一行读取并处理文件时间
混乱的数据：您可能必须改进或替换爆炸语句
不安全的数据：参数化查询并添加检查以避免 SQL 注入攻击
缓慢：您正在进行大量数据库调用；考虑首先描述的方法而不是
脆弱的：您可以添加检查以查看更新是否成功

请参阅 http:// php.net/manual/function.explode.php 和 mysql-query 文档也有更多信息

You could try loading the data into a temporary table, running the update, then dropping the temp table - something like:

CREATE TABLE dftmp(id VARCHAR(255), name VARCHAR(255));
LOAD DXTX INFILE 'datafile.txt' INTO TABLE dftmp(id,name);
UPDATE real_table,dftmp SET real_table.name=dftmp.name WHERE real_table.id=dftmp.id;
DROP TXBLE dftmp;

(replace DXTX with DATA and TXBLE with TABLE - it's that way above because my work has a filtering system in place)

See http://dev.mysql.com/doc/refman/4.1/en/load-data.html for more info on LOAD DATA.

Handling this primarily in PHP would likely be slower, but if you need to go that route then it would be something like:

# make database connection

# open file and get it's contents
$lines = file('datafile.txt');

# for each line in the file
#     split the line into its parts
#     update the DB
foreach ($lines as $line) {
    $line_ar = explode("/\s+/",$line);
    mysql_query("UPDATE t SET name='$line_ar[1]' WHERE id='$line_ar[0]'");
}

# close the database connection

Potential gotchas for the above code:

really big files : you'd need to read and handle the file one line at a time
messy data : you might have to refine or replace the explode statement
unsafe data : parametrize the query and add checking to avoid SQL injection attacks
slow : you're making a lot of db calls; consider the approach first described instead
fragile : you could add checking to see whether the update succeeded

See http://php.net/manual/function.explode.php and the mysql-query docs there as well for more info

回复收藏 0 原文

逆光下的微笑 2024-10-29 15:30:30

如果您确定它始终是 5 个字符，您可以简单地使用：

<?php
$ID = substr($varToEachLine, 0, 5);
?>

If you're sure it's always 5 characters, you could simply use:

<?php
$ID = substr($varToEachLine, 0, 5);
?>

回复收藏 0 原文

鸢与 2024-10-29 15:30:30

您可以使用 explode() 将字符串拆分为行；然后，对于每一行，一个简单的正则表达式，其中 preg_match()功能，以匹配您感兴趣的部分。

例如，这样的事情：

$str = <<<STR
22222   hihihi
33333   hihihi
kjhkh   hihihi
STR;

foreach (explode(PHP_EOL, $str) as $line) {
  if (preg_match('/^([^\s]+)\s+(.*)$/', $line, $m)) {
    var_dump($m[1], $m[2]);
  }
}

会让你：

string '22222' (length=5)
string 'hihihi' (length=6)
string '33333' (length=5)
string 'hihihi' (length=6)
string 'kjhkh' (length=5)
string 'hihihi' (length=6)

关于我使用的正则表达式的注释：

从字符串开头开始：^
匹配非白色字符的任何内容（制表符、换行符、空格）：[^\s]
- 一次或多次：[^\s]+
- 捕获它：([^\s]+)
匹配任何白色字符：\s
- 一次或多次：\s+
匹配任意次数的任何内容：.*
- 捕获它：(.*)
，最后，字符串结尾：$

现在，当您使用文件而不是字符串作为输入时，您可能希望逐行读取文件，而不是将整个文件提取到内存中；为此，请参阅 fgets() 函数 - 有一个示例它的手册页。

Your could use explode() to split your string into lines ; and, then, for each line, a simple Regular expression, with the preg_match() function, to match the parts that interest you.

For example, something like this :

$str = <<<STR
22222   hihihi
33333   hihihi
kjhkh   hihihi
STR;

foreach (explode(PHP_EOL, $str) as $line) {
  if (preg_match('/^([^\s]+)\s+(.*)$/', $line, $m)) {
    var_dump($m[1], $m[2]);
  }
}

Would get you :

string '22222' (length=5)
string 'hihihi' (length=6)
string '33333' (length=5)
string 'hihihi' (length=6)
string 'kjhkh' (length=5)
string 'hihihi' (length=6)

Notes about the regex I used :

starts at beginning of string : ^
matches anything that is not a white character (tabulation, newline, space) : [^\s]
- one or more times : [^\s]+
- capturing it : ([^\s]+)
matches anything that is a white character : \s
- one or more times : \s+
matches anything any number of times : .*
- capturing it : (.*)
and, finally, end of string : $

Now, as you are working with a file as input, and not a string, you might want to read the file line by line, not fetching the whole file into memory ; for that, see the fgets() function -- there is an example on its manual page.

回复收藏 0 原文

~没有更多了~