1. 个人技术分享首页

浅谈postgresql数据库varchar、char、text的比较

如下所示:

名字 描述
character varying(n), varchar(n) 变长,有长度限制
character(n), char(n) 定长,不足补空白
text 变长,无长度限制

简单来说,varchar的长度可变,而char的长度不可变,对于postgresql数据库来说varchar和char的区别仅仅在于前者是变长,而后者是定长,最大长度都是10485760(1GB)

varchar不指定长度,可以存储最大长度(1GB)的字符串,而char不指定长度,默认则为1,这点需要注意。

text类型:在postgresql数据库里边,text和varchar几乎无性能差别,区别仅在于存储结构的不同

对于char的使用,应该在确定字符串长度的情况下使用,否则应该选择varchar或者text

官方解读:

SQL定义了两种基本的字符类型:character varying(n) 和character(n),这里的n 是一个正整数。两种类型都可以存储最多n个字符的字符串(没有字节)。试图存储更长的字符串到这些类型的字段里会产生一个错误,除非超出长度的字符都是空白,这种情况下该字符串将被截断为最大长度。这个看上去有点怪异的例外是SQL标准要求的。如果要存储的字符串比声明的长度短,类型为character的数值将会用空白填满;而类型为character varying的数值将只是存储短些的字符串。

如果我们明确地把一个数值转换成character varying(n) 或character(n),那么超长的数值将被截断成n 个字符,且不会抛出错误。这也是SQL标准的要求。

varchar(n)和char(n) 分别是character varying(n) 和character(n)的别名,没有声明长度的character等于character(1);如果不带长度说明词使用character varying,那么该类型接受任何长度的字符串。后者是PostgreSQL的扩展。

另外,PostgreSQL提供text类型,它可以存储任何长度的字符串。尽管类型text不是SQL 标准,但是许多其它SQL数据库系统也有它。

character类型的数值物理上都用空白填充到指定的长度n,并且以这种方式存储和显示。不过,填充的空白是无语意的。在比较两个character 值的时候,填充的空白都不会被关注,在转换成其它字符串类型的时候, character值里面的空白会被删除。请注意,在character varying和text数值里,结尾的空白是有语意的。并且当使用模式匹配时,如LIKE,使用正则表达式。

一个简短的字符串(最多126个字节)的存储要求是1个字节加上实际的字符串,其中包括空格填充的character。更长的字符串有4个字节的开销,而不是1。长的字符串将会自动被系统压缩,因此在磁盘上的物理需求可能会更少些。更长的数值也会存储在后台表里面,这样它们就不会干扰对短字段值的快速访问。不管怎样,允许存储的最长字符串大概是1GB 。允许在数据类型声明中出现的n 的最大值比这还小。修改这个行为没有什么意义,因为在多字节编码下字符和字节的数目可能差别很大。如果你想存储没有特定上限的长字符串,那么使用text 或没有长度声明的character varying,而不要选择一个任意长度限制。

提示: 这三种类型之间没有性能差别,除了当使用填充空白类型时的增加存储空间,和当存储长度约束的列时一些检查存入时长度的额外的CPU周期。虽然在某些其它的数据库系统里,character(n) 有一定的性能优势,但在PostgreSQL里没有。事实上,character(n)通常是这三个中最慢的,因为额外存储成本。在大多数情况下,应该使用text 或character varying。

补充:使用PostGreSQL数据库进行text录入和text检索

中文分词

ChineseParse.cs

using System;
using System.Collections;
using System.IO;
using System.Text.RegularExpressions;
namespace FullTextSearch.Common
{
  /// 
  ///   中文分词器。
  /// 
  public class ChineseParse
  {
    private static readonly ChineseWordsHashCountSet _countTable;
    static ChineseParse()
    {
      _countTable = new ChineseWordsHashCountSet();
      InitFromFile("ChineseDictionary.txt");
    }
    /// 
    ///   从指定的文件中初始化中文词语字典和字符串次数字典。
    /// 
    /// 文件名
    private static void InitFromFile(string fileName)
    {
      string path = Path.Combine(Directory.GetCurrentDirectory(), @"....Common", fileName);
      if (File.Exists(path))
      {
        using (StreamReader sr = File.OpenText(path))
        {
          string s = "";
          while ((s = sr.ReadLine()) != null)
          {
            ChineseWordUnit _tempUnit = InitUnit(s);
            _countTable.InsertWord(_tempUnit.Word);
          }
        }
      }
    }
    /// 
    ///   将一个字符串解析为ChineseWordUnit。
    /// 
    /// 字符串
    /// 解析得到的ChineseWordUnit
    /// 4
    /// 0
    private static ChineseWordUnit InitUnit(string s)
    {
      var reg = new Regex(@"s+");
      string[] temp = reg.Split(s);
      //if (temp.Length != 2)
      //{
      //  throw new Exception("字符串解析错误:" + s);
      //}
      if (temp.Length != 1)
      {
        throw new Exception("字符串解析错误:" + s);
      }
      return new ChineseWordUnit(temp[0], Int32.Parse("1"));
    }
    /// 
    ///   分析输入的字符串,将其切割成一个个的词语。
    /// 
    /// 待切割的字符串
    /// 所切割得到的中文词语数组
    public static string[] ParseChinese(string s)
    {
      int _length = s.Length;
      string _temp = String.Empty;
      var _words = new ArrayList();
      for (int i = 0; i  1)
        {
          int j = 2;
          for (; i + j  0; j++)
          {
          }
          _temp = s.Substring(i, j - 1);
          i = i + j - 2;
        }
        i++;
        _words.Add(_temp);
      }
      var _tempStringArray = new string[_words.Count];
      _words.CopyTo(_tempStringArray);
      return _tempStringArray;
    }
  }
}

ChineseWordsHashCountSet.cs

using System.Collections;
namespace FullTextSearch.Common
{
  /// 
  ///   记录字符串出现在中文字典所录中文词语的前端的次数的字典类。如字符串"中"出现在"中国"的前端,则在字典中记录一个次数。
  /// 
  public class ChineseWordsHashCountSet
  {
    /// 
    ///   记录字符串在中文词语中出现次数的Hashtable。键为特定的字符串,值为该字符串在中文词语中出现的次数。
    /// 
    private readonly Hashtable _rootTable;
    /// 
    ///   类型初始化。
    /// 
    public ChineseWordsHashCountSet()
    {
      _rootTable = new Hashtable();
    }
    /// 
    ///   查询指定字符串出现在中文字典所录中文词语的前端的次数。
    /// 
    /// 指定字符串
    /// 字符串出现在中文字典所录中文词语的前端的次数。若为-1,表示不出现。
    public int GetCount(string s)
    {
      if (!_rootTable.ContainsKey(s.Length))
      {
        return -1;
      }
      var _tempTable = (Hashtable) _rootTable[s.Length];
      if (!_tempTable.ContainsKey(s))
      {
        return -1;
      }
      return (int) _tempTable[s];
    }
    /// 
    ///   向次数字典中插入一个词语。解析该词语,插入次数字典。
    /// 
    /// 所处理的字符串。
    public void InsertWord(string s)
    {
      for (int i = 0; i 
    ///   向次数字典中插入一个字符串的次数记录。
    /// 
    /// 所插入的字符串。
    private void InsertSubString(string s)
    {
      if (!_rootTable.ContainsKey(s.Length) && s.Length > 0)
      {
        var _newHashtable = new Hashtable();
        _rootTable.Add(s.Length, _newHashtable);
      }
      var _tempTable = (Hashtable) _rootTable[s.Length];
      if (!_tempTable.ContainsKey(s))
      {
        _tempTable.Add(s, 1);
      }
      else
      {
        _tempTable[s] = (int) _tempTable[s] + 1;
      }
    }
  }
}

ChineseWordUnit.cs

namespace FullTextSearch.Common
{
  public struct ChineseWordUnit
  {
    private readonly int _power;
    private readonly string _word;
    /// 
    ///   结构初始化。
    /// 
    /// 中文词语
    /// 该词语的权重
    public ChineseWordUnit(string word, int power)
    {
      _word = word;
      _power = power;
    }
    /// 
    ///   中文词语单元所对应的中文词。
    /// 
    public string Word
    {
      get { return _word; }
    }
    /// 
    ///   该中文词语的权重。
    /// 
    public int Power
    {
      get { return _power; }
    }
  }
}

ChineseDictionary.txt

浅谈postgresql数据库varchar、char、text的比较

主窗体界面

MainManager.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using FullTextSearch.Common;
using Npgsql;
namespace FullTextSearch
{
  public partial class MainManager : Form
  {
    private readonly PostgreSQL pg = new PostgreSQL();
    private readonly SQLquerys sqlQuerys = new SQLquerys();
    private char analysisType;
    private string createConnString = "";
    private DataSet dataSet = new DataSet();
    private DataTable dataTable = new DataTable();
    private char odabirAndOr;
    private char vrstaPretrazivanja;
    public MainManager()
    {
      InitializeComponent();
      rbtn_AND.Checked = true;
      rbtnNeizmjenjeni.Checked = true;
      odabirAndOr = '*';
      radioButton_Day.Checked = true;
      radioButton_Day.Checked = true;
    }
    private void Form1_Load(object sender, EventArgs e)
    {
      gb_unosPodataka.Enabled = false;
      groupBox_Search.Enabled = false;
      groupBox_Analysis.Enabled = false;
      button_Disconnect.Enabled = false;
      button_Pretrazi.BackColor = Color.WhiteSmoke;
      button_Disconnect.BackColor = Color.WhiteSmoke;
      button_unosTekstaUBazu.BackColor = Color.WhiteSmoke;
      button1.BackColor = Color.WhiteSmoke;
    }
    private void button_unosTekstaUBazu_Click(object sender, EventArgs e)
    {
      string searchTextBoxString = rTB_unosTextaUBazu.Text;
      if (searchTextBoxString != "")
      {
        pg.insertIntoTable(searchTextBoxString, pg.conn);
        MessageBox.Show(searchTextBoxString + " 添加到数据库!");
        rTB_unosTextaUBazu.Clear();
      }
      else
      {
        MessageBox.Show("不允许空数据!");
      }
    }
    private void button_Pretrazi_Click(object sender, EventArgs e)
    {
      string stringToSearch;
      string sql;
      string highlitedText;
      string rank;
      string check;
      stringToSearch = txt_Search.Text.Trim();
      var list = new List(ChineseParse.ParseChinese(stringToSearch));
      ;
      sql = sqlQuerys.createSqlString(list, odabirAndOr, vrstaPretrazivanja);
      richTextBox1.Text = sql;
      check = sqlQuerys.testIfEmpty(stringToSearch);
      pg.insertIntoAnalysisTable(stringToSearch, pg.conn);
      pg.openConnection();
      var command = new NpgsqlCommand(sql, pg.conn);
      NpgsqlDataReader reader = command.ExecuteReader();
      int count = 0;
      linkLabel_Rezultat.Text = " ";
      while (reader.Read())
      {
        highlitedText = reader[1].ToString();
        rank = reader[3].ToString();
        linkLabel_Rezultat.Text += highlitedText + "[" + rank + "]n";
        count++;
      }
      labelBrojac.Text = "找到的文件数量: " + count;
      pg.closeConnection();
    }
    private void rbtn_AND_CheckedChanged(object sender, EventArgs e)
    {
      odabirAndOr = '*';
    }
    private void rbtn_OR_CheckedChanged(object sender, EventArgs e)
    {
      odabirAndOr = '+';
    }
    private void rbtnNeizmjenjeni_CheckedChanged(object sender, EventArgs e)
    {
      vrstaPretrazivanja = 'A';
    }
    private void rbtn_Rijecnici_CheckedChanged(object sender, EventArgs e)
    {
      vrstaPretrazivanja = 'B';
    }
    private void rbtn_Fuzzy_CheckedChanged(object sender, EventArgs e)
    {
      vrstaPretrazivanja = 'C';
    }
    private void button_Connect_Click(object sender, EventArgs e)
    {
      if (connectMe())
      {
        gb_unosPodataka.Enabled = true;
        groupBox_Search.Enabled = true;
        groupBox_Analysis.Enabled = true;
        textBox_Database.Enabled = false;
        textBox_IP.Enabled = false;
        textBox_Port.Enabled = false;
        textBox_Password.Enabled = false;
        textBox_UserID.Enabled = false;
        button_Connect.Enabled = false;
        button_Disconnect.Enabled = true;
        button_Pretrazi.BackColor = Color.SkyBlue;
        button_Disconnect.BackColor = Color.IndianRed;
        button_unosTekstaUBazu.BackColor = Color.MediumSeaGreen;
        button1.BackColor = Color.MediumSeaGreen;
        button_Connect.BackColor = Color.WhiteSmoke;
      }
    }
    private void button_Disconnect_Click(object sender, EventArgs e)
    {
      gb_unosPodataka.Enabled = false;
      groupBox_Search.Enabled = false;
      groupBox_Analysis.Enabled = false;
      textBox_Database.Enabled = true;
      textBox_IP.Enabled = true;
      textBox_Port.Enabled = true;
      textBox_Password.Enabled = true;
      textBox_UserID.Enabled = true;
      button_Connect.Enabled = true;
      button_Disconnect.Enabled = false;
      button_Pretrazi.BackColor = Color.WhiteSmoke;
      button_Disconnect.BackColor = Color.WhiteSmoke;
      button_unosTekstaUBazu.BackColor = Color.WhiteSmoke;
      button1.BackColor = Color.WhiteSmoke;
      button_Connect.BackColor = Color.MediumSeaGreen;
      txt_Search.Text = "";
      linkLabel_Rezultat.Text = "";
      richTextBox1.Text = "";
      labelBrojac.Text = "";
    }
    private bool connectMe()
    {
      createConnString += "Server=" + textBox_IP.Text + ";Port=" + textBox_Port.Text + ";User Id=" +
                textBox_UserID.Text + ";Password=" + textBox_Password.Text + ";Database=" +
                textBox_Database.Text + ";";
      sqlQuerys.setTheKey(createConnString);
      pg.setConnectionString();
      pg.setConnection();
      if (pg.openConnection())
      {
        MessageBox.Show("您已成功连接!");
        pg.closeConnection();
        return true;
      }
      return false;
    }
    private void button1_Click(object sender, EventArgs e)
    {
      string selectedTimestamp;
      selectedTimestamp = dateTimePicker_From.Value.ToString("dd-MM-yyyy hh:mm:ss") + " " +
                dateTimePicker_To.Value.ToString("dd-MM-yyyy hh:mm:ss");
      var analize = new Analysis(selectedTimestamp, analysisType);
      analize.Show();
    }
    private void radioButton_Day_CheckedChanged(object sender, EventArgs e)
    {
      analysisType = 'D';
    }
    private void radioButton_Hour_CheckedChanged(object sender, EventArgs e)
    {
      analysisType = 'H';
    }
  }
}

SQLquerys.cs代码:

using System.Collections.Generic;
namespace FullTextSearch
{
  internal class SQLquerys
  {
    private static string giveMeTheKey;
    private static int tempInt = 1;
    //设置连接字符串
    public void setTheKey(string connString)
    {
      giveMeTheKey = connString;
      giveMeTheKey += "";
    }
    //将连接字符串存储在静态变量中
    public string getTheKey()
    {
      giveMeTheKey += "";
      return giveMeTheKey;
    }
    public void setCounter()
    {
      tempInt = 1;
    }
    //根据AND和OR的选择分析字符串进行搜索
    public string createFunctionString(List searchList, char selector)
    {
      string TempString = "";
      string[] TempField = null;
      int i = 0;
      int j = 0;
      foreach (string searchStringInList in searchList)
      {
        if (j != 0)
        {
          if (selector == '+')
            TempString = TempString + " | ";
          else if (selector == '*')
            TempString = TempString + " & ";
        }
        j = 1;
        TempField = splitListForInput(searchStringInList);
        TempString = TempString + "(";
        foreach (string justTempString in TempField)
        {
          if (i != 0)
          {
            TempString = TempString + " & ";
          }
          TempString = TempString + justTempString;
          i = 1;
        }
        TempString = TempString + ")";
        i = 0;
      }
      return TempString;
    }
    //帮助方法
    public List splitInputField(string[] inputField)
    {
      var unfinishedList = new List();
      foreach (string splitString in inputField)
      {
        unfinishedList.Add(splitString);
      }
      return unfinishedList;
    }
    //帮助方法
    public string[] splitListForInput(string inputString)
    {
      string[] parsedList = null;
      parsedList = inputString.Split(' ');
      return parsedList;
    }
    //在PostgreSQL中创建ts功能的功能,用于字典搜索
    public string createTsFunction(string tsString)
    {
      string tsHeadline = "";
      string tsRank = "";
      string tsFunction = "";
      tsHeadline = ",n ts_headline("content", to_tsquery('" + tsString + "')), "content"";
      tsRank = ",n ts_rank(to_tsvector("content"), to_tsquery('" + tsString + "')) rank";
      tsFunction = tsHeadline + tsRank;
      return tsFunction;
    }
    //创建SQL查询依赖于选择哪种类型的搜索,也取决于AND或OR选择器
    public string createSqlString(List searchList, char selector, char vrstaPretrazivanja)
    {
      string selectString = "";
      string myTempString = "";
      string TempString = "";
      int i = 0;
      TempString = createFunctionString(searchList, selector);
      TempString = createTsFunction(TempString);
      selectString = "SELECT "id"" + TempString + "nFROM "texttable" nWHERE ";
      if (vrstaPretrazivanja == 'A')
      {
        foreach (string myString in searchList)
        {
          if (i == 0)
          {
            myTempString = myTempString + ""content" LIKE '%" + myString + "%' ";
            i++;
          }
          else
          {
            if (selector == '*')
              myTempString = myTempString + "nAND "content" LIKE '%" + myString + "%' ";
            else if (selector == '+')
              myTempString = myTempString + "nOR "content" LIKE '%" + myString + "%' ";
          }
        }
      }
      else if (vrstaPretrazivanja == 'B')
      {
        foreach (string myString in searchList)
        {
          string temporalString = "";
          string[] testingString = myString.Split(' ');
          for (int k = 0; k 

PostgreSQL.cs代码:

using System;
using System.Windows.Forms;
using Npgsql;
using NpgsqlTypes;
namespace FullTextSearch
{
  public class PostgreSQL
  {
    private static int tempInt = 1;
    private readonly SQLquerys sql = new SQLquerys();
    public NpgsqlConnection conn;
    public string connectionstring;
    private string newConnString;
    public PostgreSQL()
    {
      setConnectionString();
      setConnection();
    }
    public void setConnectionString()
    {
      newConnString = sql.getTheKey();
      connectionstring = String.Format(newConnString);
      setConnection();
    }
    public void setConnection()
    {
      conn = new NpgsqlConnection(connectionstring);
    }
    public bool openConnection()
    {
      try
      {
        conn.Open();
        return true;
      }
      catch
      {
        MessageBox.Show("Unable to connect! Check parameters!");
        return false;
      }
    }
    public void closeConnection()
    {
      conn.Close();
    }
    public void insertIntoTable(string textToInsert, NpgsqlConnection nsqlConn)
    {
      string mySqlString = "INSERT INTO "texttable" ("content") VALUES (@Param1)";
      var myParameter = new NpgsqlParameter("@Param1", NpgsqlDbType.Text);
      myParameter.Value = textToInsert;
      openConnection();
      var myCommand = new NpgsqlCommand(mySqlString, nsqlConn);
      myCommand.Parameters.Add(myParameter);
      myCommand.ExecuteNonQuery();
      closeConnection();
    }
    public void insertIntoAnalysisTable(string textToInsert, NpgsqlConnection nsqlConn)
    {
      string dateTime = DateTime.Now.ToString();
      string[] temp;
      temp = dateTime.Split(' ');
      string mySqlString =
        "INSERT INTO "analysistable" ("searchedtext", "dateofsearch", "timeofsearch") VALUES ('" +
        textToInsert + "', '" + temp[0] + "'" + ", '" + temp[1] + "');";
      openConnection();
      var myCommand = new NpgsqlCommand(mySqlString, nsqlConn);
      myCommand.ExecuteNonQuery();
      closeConnection();
    }
    public void executeQuery(string queryText, NpgsqlConnection nsqlConn)
    {
      openConnection();
      var myCommand = new NpgsqlCommand(queryText, nsqlConn);
      myCommand.ExecuteNonQuery();
      closeConnection();
    }
    public void createTempTable(NpgsqlConnection nsqlConn, char analysisType, string dateFrom, string dateTo,
      string splitMe)
    {
      if (analysisType == 'H')
      {
        string dropIfExists = "DROP TABLE IF EXISTS "sat";";
        string createTempTable = "CREATE TABLE IF NOT EXISTS "sat" (rbrSata INT);";
        string insertIntoTempTable = "";
        for (int i = 0; i 

PostGreSQL sql脚本:

CREATE TABLE public.analysistable
(
  id integer NOT NULL DEFAULT nextval('analysistable_id_seq'::regclass),
  searchedtext text COLLATE pg_catalog."default" NOT NULL,
  dateofsearch date NOT NULL,
  timeofsearch time without time zone NOT NULL,
  CONSTRAINT analysistable_pkey PRIMARY KEY (id)
)
WITH (
  OIDS = FALSE
)
TABLESPACE pg_default;
ALTER TABLE public.analysistable
  OWNER to king;

浅谈postgresql数据库varchar、char、text的比较

运行结果如图:

浅谈postgresql数据库varchar、char、text的比较

浅谈postgresql数据库varchar、char、text的比较

浅谈postgresql数据库varchar、char、text的比较

浅谈postgresql数据库varchar、char、text的比较

文章来源于互联网:浅谈postgresql数据库varchar、char、text的比较

原创文章,作者:admin,如若转载,请注明出处:https://www.aliyunsolution.com/3080.html